Looking for an example on database triggers from Xataface

[rleyba]

Hi Steve,

I had an inquiry on my post last Jan 1 ---> viewtopic.php?f=4&t=5650 where I was looking for a way to setup triggers in Xataface. I saw a reference in some of your documentation but I didn´t see an actual example with which to have a starting point. Do you have an actual example (insert trigger is what I need to do) I can use to get started?

thanks and regards

[shannah]

Here is an article I whipped up for the beforeSave trigger. Hopefully this helps.

http://xataface.com/wiki/beforeSave

-Steve

[rleyba]

Thanks Steve,

This will be very helpful indeed.

[rleyba]

Hi Steve,

Thanks for your example in the post. I did a search of the xataface forums for other trigger examples which I would need for my requirements, but I am getting stuck with the syntax.Can I just ask for help with the syntax of the PHP trigger statements? Basically, I have two tables linked together many-to-many, in exactly the same way that your Courses/Programs tutorial works. The table joining the SERVERS (IJWTF_IP) and SERVER_IP_ADDRESSES table is the
SERVERTOIP table. So every time I link a server to an IP address, an entry gets created in the SERVERTOIP table. I am trying to create a trigger such that every time an entry on this SERVERTOIP table gets created or updated, it would populate the SERVER_IP_ADDRESSES.SERVER_NAME with the Name of the server it is being linked to from the IJWTF_IP(which is the server table).

My syntax is below, I know it is wrong, but I just don´t know how to fix it.

Thanks very much.





<?
class tables_SERVERTOIP {
function beforeSave($record){
$sqlresult = mysql_query"(Update SERVER_IP_ADDRESS
SET SERVERNAME = ("SELECT IJWTF_IP.SERVER_NAME FROM SERVERTOIP, IJWTF_IP WHERE IJWTF_IP.IJWTF_IP_KEY =
SERVERTOIP.SERVER_ID and SERVERTOIP.IP_ID = '$IP_ID'")");
$record->setValue('IJWTF_IP.SERVER_NAME',$sqlresult);

}
}

?>

------------------Table schema-------------------
CREATE TABLE IF NOT EXISTS `SERVERTOIP` (
`SERVER_ID` int(11) NOT NULL,
`IP_ID` int(11) NOT NULL,
PRIMARY KEY (`SERVER_ID`,`IP_ID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;


CREATE TABLE IF NOT EXISTS `IJWTF_IP` (
`IJWTF_IP_KEY` int(11) NOT NULL auto_increment COMMENT 'Primary Key',
`SERVER_NAME` varchar(20) default NULL,
`OS_NAME` varchar(10) NOT NULL,
`SERVER_ID` char(12) NOT NULL,
`COMMENTS` varchar(255) NOT NULL,
PRIMARY KEY (`IJWTF_IP_KEY`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=2 ;



CREATE TABLE IF NOT EXISTS `SERVER_IP_ADDRESS` (
`IP_ID` int(11) NOT NULL auto_increment,
`IP_ADDRESS` char(15) NOT NULL,
`MAC` char(17) NOT NULL COMMENT 'MAC Address',
`NETMASK` char(17) NOT NULL,
`SERVER_NAME` varchar(20) default NULL COMMENT 'Populated by a trigger',
PRIMARY KEY (`IP_ID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=10 ;

[shannah]

Seems like you're a little green on PHP and are thinking in terms of SQL type triggers. The code that you posted gets the idea across of what you intend to do but it has quite a few syntactical problems. Since you appear to be more comfortable with SQL than PHP its probably a good idea to fold as much of your logic as possible into an SQL call rather than PHP.

First of all, you'd probably want to use the afterInsert method for this one to ensure that the insert has already occurred correctly. Then you can just run a mysql_query() with an UPDATE statement to update the rows that you want to update.

In any case you may want to refer to the mysql_query docs on the PHP site for some examples of how to query the database. http://php.net/mysql_query

-steve