none
Trigger for Insert/Update and Delete in a Main table that will log these events into a different table...Please help!

    Question

  • Hi ,

    I have a Main table Employee. During the ETL process, there are lot of inserts,updates and deletions will happen in this table.

    So, my requirement  is to create a  trigger for logging these 3 events into a different log table with a additional columns such as Event_Type and Username.

    Can you please help me on this?

    Thanks in advance!


    Tuesday, September 10, 2013 11:30 PM

Answers

  • Hi vskindia,

    First, I want to inform that one record will be inserted into inserted table if an insert action triggers the trigger, one record will be inserted into deleted table if a delete action triggers the trigger, one record will be inserted into inserted table and one record will be inserted into deleted table if an update action triggers the trigger, we can according to the inserted row account and the deleted row account to determine whether the action is insert, update or delete. You can refer to the following codes:

    use DBName;
    go
    create table A
    (
    	ID int
    	,Name varchar(20)
    )
    create table BInsert
    (
    	Username varchar(20)
    	,Event_Type char(6)
    	,[DateCol] datetime
    )
    create table CUpdate
    (
    	Username varchar(20)
    	,Event_Type char(6)
    	,[DateCol] datetime
    )
    create table DDelete
    (
    	Username varchar(20)
    	,Event_Type char(6)
    	,[DateCol] datetime
    )
    create trigger LogInserted
    on A
    after insert, update, delete
    as
    begin
    	declare @InsertRows int;
    	declare @DeleteRows int;
    	select @InsertRows=count(*) from inserted
    	select @DeleteRows=count(*) from deleted
    	if @InsertRows>0 
    	begin
    		if @DeleteRows>0
    		begin
    			insert into CUpdate(Username,Event_Type,[DateCol])
    			select CURRENT_USER,'Update',GETDATE()
    		end
    		else
    		begin
    			insert into BInsert(Username,Event_Type,[DateCol])
    			select CURRENT_USER,'Insert',GETDATE()
    		end
    	end
    	else
    	begin
    		insert into DDelete(Username,Event_Type,[DateCol])
    			select CURRENT_USER,'Delete',GETDATE()
    	end
    end
    insert into a values(1,'a');
    select * from BInsert;
    select * from CUpdate;
    select * from DDelete;
    update a
    set name = 'changed'
    where ID = 1;
    select * from BInsert;
    select * from CUpdate;
    select * from DDelete;
    delete from a
    where ID = 1;
    select * from BInsert;
    select * from CUpdate;
    select * from DDelete;
    drop table A;
    drop table BInsert;
    drop table CUpdate;
    drop table DDelete;

    Allen Li
    TechNet Community Support

    Thursday, September 12, 2013 8:23 AM
    Moderator

All replies

  • Hi vskindia,

    First, I want to inform that one record will be inserted into inserted table if an insert action triggers the trigger, one record will be inserted into deleted table if a delete action triggers the trigger, one record will be inserted into inserted table and one record will be inserted into deleted table if an update action triggers the trigger, we can according to the inserted row account and the deleted row account to determine whether the action is insert, update or delete. You can refer to the following codes:

    use DBName;
    go
    create table A
    (
    	ID int
    	,Name varchar(20)
    )
    create table BInsert
    (
    	Username varchar(20)
    	,Event_Type char(6)
    	,[DateCol] datetime
    )
    create table CUpdate
    (
    	Username varchar(20)
    	,Event_Type char(6)
    	,[DateCol] datetime
    )
    create table DDelete
    (
    	Username varchar(20)
    	,Event_Type char(6)
    	,[DateCol] datetime
    )
    create trigger LogInserted
    on A
    after insert, update, delete
    as
    begin
    	declare @InsertRows int;
    	declare @DeleteRows int;
    	select @InsertRows=count(*) from inserted
    	select @DeleteRows=count(*) from deleted
    	if @InsertRows>0 
    	begin
    		if @DeleteRows>0
    		begin
    			insert into CUpdate(Username,Event_Type,[DateCol])
    			select CURRENT_USER,'Update',GETDATE()
    		end
    		else
    		begin
    			insert into BInsert(Username,Event_Type,[DateCol])
    			select CURRENT_USER,'Insert',GETDATE()
    		end
    	end
    	else
    	begin
    		insert into DDelete(Username,Event_Type,[DateCol])
    			select CURRENT_USER,'Delete',GETDATE()
    	end
    end
    insert into a values(1,'a');
    select * from BInsert;
    select * from CUpdate;
    select * from DDelete;
    update a
    set name = 'changed'
    where ID = 1;
    select * from BInsert;
    select * from CUpdate;
    select * from DDelete;
    delete from a
    where ID = 1;
    select * from BInsert;
    select * from CUpdate;
    select * from DDelete;
    drop table A;
    drop table BInsert;
    drop table CUpdate;
    drop table DDelete;

    Allen Li
    TechNet Community Support

    Thursday, September 12, 2013 8:23 AM
    Moderator
  • Allen,

    I believe Vskindia has resolved the issue by this!

    http://social.msdn.microsoft.com/Forums/en-US/0b5fe97d-89eb-426b-887c-0170b177e03b/delete-trigger-not-working-please-help


    Many Thanks & Best Regards, Hua Min


    Thursday, September 12, 2013 8:44 AM