SQL Server Performance Forum – Threads Archive
Trigger help appreciated
Greetings – <br /><br />I am working on a trigger that watches a table and when an edit occurs, the record is copied to another table. It works fine.<br /><br />What I’d like it to do is only update when the date_modified field is changed.<br /><br />Here’s what I’ve got now:<br />CREATE TRIGGER FM_Insert_Location<br />ON spemp<br />FOR INSERT<br />AS<br /><br />DECLARE @User_ID VARCHAR(10)<br />DECLARE @Building VARCHAR(16) <br />DECLARE @Floor VARCHAR(<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ /> <br />DECLARE @ColumnNumber VARCHAR(16)<br />DECLARE @Status CHAR(10) <br />DECLARE @EventDateTime DATETIME<br /><br />SELECT @User_ID = (SELECT spemp_code FROM Inserted)<br />SELECT @Building = (SELECT spbldinf_code FROM Inserted)<br />SELECT @Floor = (SELECT spfloors_code FROM Inserted)<br />SELECT @ColumnNumber = (SELECT spbldrom_code FROM Inserted)<br />SELECT @Status = "N"<br />SELECT @EventDateTime = (SELECT date_modified FROM Inserted)<br /><br />INSERT FM_Location values (@User_ID, @Building, @Floor, @ColumnNumber, @Status, @EventDateTime)<br /><br /><br />I have a feeling that what I should do is something like this:<br />IF EXISTS (SELECT *<br /> FROM inserted a<br /> JOIN deleted b ON a.spemp_code = b.spemp_code<br /> WHERE b.date_modified > a.date_modified<br /> BEGIN<br />INSERT FM_Location values (@User_ID, @Building, @Floor, @ColumnNumber, @Status, @EventDateTime)<br /> END<br /><br />Am I on the right track or is there an easier way? Any advice is appreciated.<br />Thanks in advance,<br />-RobertCREATE TRIGGER FM_Insert_Location<br />ON spemp<br />FOR Update<br />AS<br /><br />DECLARE @User_ID VARCHAR(10)<br />DECLARE @Building VARCHAR(16) <br />DECLARE @Floor VARCHAR(<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ /> <br />DECLARE @ColumnNumber VARCHAR(16)<br />DECLARE @Status CHAR(10) <br />DECLARE @EventDateTime DATETIME<br /><br />IF UPDATE(date_modified)———Here is the key<br /><br />SELECT @User_ID = (SELECT spemp_code FROM Inserted)<br />SELECT @Building = (SELECT spbldinf_code FROM Inserted)<br />SELECT @Floor = (SELECT spfloors_code FROM Inserted)<br />SELECT @ColumnNumber = (SELECT spbldrom_code FROM Inserted)<br />SELECT @Status = "N"<br />SELECT @EventDateTime = (SELECT date_modified FROM Inserted)<br /><br />INSERT FM_Location values (@User_ID, @Building, @Floor, @ColumnNumber, @Status, @EventDateTime)……………………………………Bla bla bla<br /><br /><br />Raulie<br />Hewlett-Packard<br />
Here are the ways to check for column updates.
1)IF UPDATE(date_modified)———Here is the key –As Raulie said
2)IF (COLUMNS_UPDATED())
3)Check from deleted/inserted table Rajeev Kumar Srivastava
–ALWAYS BE POSITIVE!–
]]>