February 1, 2010 at 9:08 am
Hi everyone,
I am not very familiar with triggers.
If we use an INSTEAD OF trigger how can we have acces, in the code of the trigger, to the WHERE clause of the statement (UPDATE, DELETE) that fired it.
We want to be able to identify the rows that should have been affected.
Regards.
Carl
February 1, 2010 at 9:16 am
carl you do not need the where clause at all inside a trigger;
The trigger there are two virtual tables named INSERTED and DELETED;they have a mirror of the columns in the table that the trigger is attached to.
they contain the rows that were affected by the INSERT/UPDATE/DELETE.
When the trigger is a INSERT trigger, the INSERTED table has all the new values; the DELETED table is not materialized in an INSERT;
similarly,in a DELETE trigger, the DELETED table has all the values of the rows being removed, just before they are actually removed. .there's no INSERTED table in a delete
an UPDATE trigger has both tables populated, INSERTED with the new values, and DELETED with the old values. you can join them together on the PK/id of the table to compare old vs new values .
here's a couple of basic triggers that are keeping track of when things get updated; lame examples, but it might helkp you visualize:
Create Trigger TableName_Insert On TableName For Insert As
Update Tb
Set tb.date_created = GetDate()
From TableName Tb
Join Inserted i on Tb.PKColumn = i.PKColumn
Go
Create Trigger TableName_Update On TableName For Update As
Update Tb
Set tb.date_modified = GetDate()
From TableName Tb
Join Inserted i on Tb.PKColumn = i.PKColumn
UPDATE mt
SET DateStamp = getdate()
FROM TableName mt
JOIN Inserted i ON mt.ID = i.ID
Lowell
February 1, 2010 at 9:20 am
Thank's a lot Lowell.
I was assuming that these virtual tables were'nt populated in the cas of an INSTEAD Of Trigger.
Regards.
Carl
February 1, 2010 at 9:35 am
no the virtual tables are stiull there, but in an INSTEAD OF trigger, it's up to you to do something with the results; if you sent an did some buiz thing like email based on the results, and forgot to do the update in a trigger because you used an INSTEAD OF instead of a regular trigger, you might not find the data you emailed about
another lame example:
CREATE TABLE [dbo].[MYTEST] (
[TESTID] INT IDENTITY(1,1) NOT NULL,
[TESTTEXT] VARCHAR(30) NULL,
[UPDATEVER] TIMESTAMP NOT NULL,
CONSTRAINT [PK__myTest__30F848ED] PRIMARY KEY CLUSTERED (testid))
CREATE TRIGGER TR_mytest ON mytest
INSTEAD OF UPDATE
AS
BEGIN
SET NOCOUNT ON
SELECT * FROM INSERTED
END
INSERT INTO [mytest] ([testtext],[UpdateVer])VALUES('one',DEFAULT)
INSERT INTO [mytest] ([testtext],[UpdateVer])VALUES('Two',DEFAULT)
INSERT INTO [mytest] ([testtext],[UpdateVer])VALUES('three',DEFAULT)
--nothing happens, because the trigger did not handle the update,
--but it still fired without error
UPDATE mytest SET testtext = 'One' where testtext='one'
SELECT * FROM [myTest] --my data did not get capitalized!
Lowell
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply