March 17, 2004 at 1:26 am
Hi, all
I just built a trigger for my table, both for insert and update. The purpose is to log history of the data table to another table.
The question is, how am I gonna classify the trigger run under insert action or update action.
The triggers is similar likethis :
CREATE TRIGGER [sp_trrequest] ON [dbo].[TrRequest]
FOR INSERT, UPDATE
AS
insert into trrequest_log
select getdate(), * from inserted
... and i want to add a column in trrequest_log to identify insert or update action.
please help. thanks
March 18, 2004 at 12:29 am
If the trigger runs for insert, the deleted table will be empty. You could do this like :
CREATE TRIGGER [sp_trrequest] ON [dbo].[TrRequest]
FOR INSERT, UPDATE
AS
BEGIN
DECLARE @ins int, @del int, @type varchar(30)
select @ins = count(*) from inserted
select @del = count(*) from deleted
if @ins = 0 AND @del = 0 return -- nothing updated/deleted
if @del = 0 set @type = 'insert' else set @type = 'update'
if @type = 'update'
begin
-- perform action for update
print 'update'
end
else
begin
-- perform action for insert
print 'insert'
end
end
March 18, 2004 at 4:39 pm
If you just need a value, without other logic, try:
select getdate(), CASE WHEN EXISTS(SELECT 1 FROM deleted) THEN 'update' ELSE 'insert' END, * from inserted
The EXISTS clause is faster than counting the rows.
March 19, 2004 at 6:12 pm
Simple answer & most overlooked;is that you can create a trigger for each event like this:
CREATE TRIGGER [sp_INS_trrequest] ON [dbo].[TrRequest]
FOR INSERT
AS
BEGIN
CREATE TRIGGER [sp_UPD_trrequest] ON [dbo].[TrRequest]
FOR UPDATE
AS
BEGIN
Faster, easier to debug, & specific to event!
Coach James
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply