Update triggers
-
HI all,
I'm new to sql and would be grateful if someone could tell me how to write update triggers?
Thanks
Wade Montague
-
Hi Wade,
The basic syntax (see BOL for more details) looks like this:
***********
create trigger u_testtable for update on testtable as
--comments/owner here
--always a good idea to include this
set nocount on
select f1, f2 from inserted
***********
Key points to note are that a trigger fires once per transaction, not once per row! Inside the trigger you have access to two "logical" tables called inserted and deleted that contain the after and before states of the rows affected, respectively. Text fields are not visible in triggers. Typically a trigger should be a very short lightweight action.
Give it a try, if you get stuck post your code, we'll be glad to help!
Andy
-
I just assumed that would be three logical tables, inserted to retrieve data after an insert, deleted for a delete
and updated for an update. If this is not the case, how do I get data after an update has occured?
P.S I should have mentioned, I am using SQL Server 7.0, if that makes a difference.
-
You use the combination of inserted and deleted. For an update both are populated. Inserted has what the record(s) looked like AFTER the update was applied, the deleted table has what they looked like BEFORE the update. You can compare the two by joining on the primary key(s).
For example, if your primarykey is "pkey" then:
select i.* from inserted i inner join deleted d on i.pkey=d.pkey where i.amountsold=0
There are also some constructs you can use in the trigger to check a specific column like if update().
Andy
-
Thanks, sorry and also,CREATE TRIGGER [tr_addtotimeline] ON [paper_orders]
FOR INSERT
AS
DECLARE @publication_id INT
DECLARE @delivery_date DATETIME
/* get pub_id and date from the inserted data */
SELECT @publication_id AS publication_id, @delivery_date AS delivery_date
FROM inserted
/* debug only */
PRINT @publication_id
PRINT @delivery_date
This returns NULL any idears why
-
sorry again,
got it sorted thanks for the help
wade montague
Edited by - wade on 02/18/2002 08:59:54 AM
-
Thanks for the update. For anyone reading, the correct way to do this is:
CREATE TRIGGER [tr_addtotimeline] ON [paper_orders]
FOR INSERT
AS
DECLARE @publication_id INT
DECLARE @delivery_date DATETIME
/* get pub_id and date from the inserted data */
SELECT publication_id, delivery_date
FROM inserted
Steve Jones
-
Except that it is an insert and not an update trigger!
Andy
Thanks, sorry and also,Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply