December 7, 2007 at 8:47 am
I want to write an Instead of trigger that applies for both inserts and updates.
How can you determine within the trigger whether it is firing for an insert or an update?
I know I could create a stored proc to do the work and then create seperate triggers for update and insert that both called the proc passing in a flag that lets the proc know whether its firing an update or an insert but I am thinking there must be a way to know without doing that.
Thanks for any help in advance.
December 7, 2007 at 9:09 am
There is no need/ way to integrate stored procedures with a trigger.
For triggers you gain access to 2 virtual tables called "Inserted" and "Deleted"
Inserted holds the data that is being Inserted, OR Updated. and the Deleted table Holds the data as it exists in the table.
So if Deleted has no Rows then you know its an insert, if it has rows then it is an update.
if the logic for Insert and update is identical then do not make 2 separate triggers just do it in one trigger.
Look up Create Trigger in books online. It will show you some simple triggers, but will show you how to look up the Inserted and Deleted Virtual tables.
December 7, 2007 at 9:40 am
You can most definitely call stored procedures from within triggers I have done it before.
You populate a number of variables from the inserted/deleted recordset and then call the proc in question passing in the variables as parameters.
So I could do this
ALTER TRIGGER trg_TEST
ON TEST
INSTEAD OF INSERT
AS
BEGIN
SET NOCOUNT ON;
DECLARE @TrigType char(1),
@ViewStatus int,
@RecordID uniqueidentifier
SELECT
@TrigType= 'I', --for insert U=update
@RecordID= RecordID,
@ViewStatus = ViewStatus
FROM
INSERTED
EXEC myDB.dbo.usp_sql_some_proc @TrigType, @RecordID, @ViewStatus
END
GO
I am needing to do a number of complex validation and checking on records entered into the table before either an insert or an update is called that cannot be done in check constraints. As there is a lot of validation code I would rather it was in one place therefore the only thing that is going to be different between the update and insert triggers is that if the validation and checking is all ok I will be doing an insert rather than an update. Therefore I'd preferablty like to just write one instead of trigger for insert and update that handled everything. To do that I need to know whether to do an insert or an update after the checking is complete. I don't want to call a stored proc and I don't really want to duplicate the code if possible. I could farm just the validation/checking part out to the proc and then return back to each trigger to do the update or insert. Its just one of those things I would like to know how to do if its possible.
Thanks
December 8, 2007 at 1:34 pm
Rob Reid (12/7/2007)
SELECT@TrigType= 'I', --for insert U=update
@RecordID= RecordID,
@ViewStatus = ViewStatus
FROM
INSERTED
That's not a good construct in a trigger. What happens when an insert affects more than one row?
Triggers fire once for a statement, not once per row.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 8, 2007 at 1:50 pm
As for your original question.
On a insert, the inserted table will be populated and the deleted will be empty. On an update, both the inserted and deleted tables will be populated. On a delete, the inserted table will be empty and the deleted will be populated.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 9, 2007 at 9:21 am
It isn't a good construct for a trigger but I was showing that you can call stored procs from triggers. To handle multiple inserts I could have while looped through each record in the inserted calling the proc that would let me know if the records were valid or not passing back an output param. If it was true I could then do the insert or update within the trigger.
The whole reason I wanted to know if it was possible to determine the action within the trigger was to a)prevent duplicating code (2 triggers) and b)having to use a stored proc (to get round having 2 triggers by passing in the action as a flag)).
Thanks for your second answer though as it does help me answer my original question which is yes I can so there is no need for either now. Ta!
December 9, 2007 at 11:25 pm
Rob Reid (12/9/2007)
To handle multiple inserts I could have while looped through each record in the inserted calling the proc
Ow! Cursors (or while loops) in triggers are very bad practice. I would contend they're worsein triggers than elsewhere dur to the entre trigger always been in a transaction.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 10, 2007 at 1:05 am
Does anybody ever bother reading the original posts before replying to messages? Notice in the original question I said "I know I could use a stored proc", I didn't say I wanted to or even that I had started writing any code I just wanted to know how I could determine within the trigger the action so as to prevent having to do something like that to get round not knowing. The whole reason I mentioned while loops and populating vars to call a proc was someone said you couldnt even call procs from triggers and it was just an example to show that yes you could. It may have not been a good example or the best thought out one I admit but it was a 5 second example.
Sorry for my monday morning rant but its only 8.00am and already there are no parking spaces left outside the office and wheres the bl@@dy milkman so I can get a coffee! Have a nice day 🙁
December 10, 2007 at 1:22 am
Yeah, I saw it. I even read it, twice.
I'm commenting on the cursor as much for the benefit of anyone reading this who might otherwise think that's a good way of writing triggers.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 10, 2007 at 4:28 am
OK I agree with you and thanks for your input.
January 14, 2008 at 12:34 pm
I commonly use this for audit triggers
create trigger t_audit_tablename
on tablename
after insert, update, delete
as
begin
set nocount on
DECLARE @strOperation AS NVARCHAR(20)
--set operation variable
IF EXISTS(SELECT * FROM inserted) AND EXISTS(SELECT * FROM deleted)
set @strOperation = 'update'
ELSE IF EXISTS(SELECT * FROM inserted) AND NOT EXISTS(SELECT * FROM deleted)
set @strOperation = 'insert'
ELSE IF NOT EXISTS(SELECT * FROM inserted) AND EXISTS(SELECT * FROM deleted)
set @strOperation = 'delete'
ELSE
set @strOperation = 'no action'
IF @strOperation = 'no action'
RETURN
END
it's more elaborate than need be whereas you can check both tables then either or etc...
January 15, 2008 at 3:32 am
Thanks for that. I've decided to do it a totally different way now anyway but its good info to know for the future.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply