Thursday 30 March 2017

Update Trigger:

----main table---
create table Trigger_update
(
Sl_no int unique identity(1,1),
Col1 varchar(30),
Col2 varchar(30),
Col3 varchar(30),
)
insert into Trigger_update values('Masum','Reza','Test1')
insert into Trigger_update values('Masum1','Reza2','Test2')
insert into Trigger_update values('Masum2','Reza3','Test3')
insert into Trigger_update values('Masum3','Reza4','Test4')

select * from Trigger_update
----Trigger table---
create table Trigger_update_trig
(
Sl_no int,
Col1 varchar(30),
Col2 varchar(30),
Col3 varchar(30),
Sys_User varchar(30),
Operation varchar(30)

)
select * from Trigger_update_trig
--Creating trigger for Update--
create trigger update_trig on Trigger_update
after update
as
declare
@sl_no int,
@col1 varchar(30),
@col2 varchar(30),
@Col3 varchar(30),
@user varchar(30),
@Operation varchar(30);

if exists(SELECT * from inserted) and exists (SELECT * from deleted)
begin
    SET @Operation = 'UPDATE';
    SET @user = SYSTEM_USER;
    SELECT @sl_no = Sl_no from inserted i;
select @col1=Col1 from inserted
select @col2=Col2 from inserted
select @Col3=Col3 from inserted
    INSERT into Trigger_update_trig(Sl_no,Col1, Col2,Col3, Sys_User,Operation) 
values (@sl_no,@col1,@col2,@Col3,@user,@Operation);
end
Conclusion :
If Update operation will exist in main table then all records will insert into trigger table.

No comments:

Post a Comment