SQL Server Performance Forum – Threads Archive

Row-based triggers

Hi
I amn having two tables Emp and EmpTrg
When i do insert/update/delete i am inserting a row into the EmpTrg table.
But when i modify multiple rows, multiple rows are not being inserted in the EmpTrg table Ex: update into emptest where id in(1,2,3,4)
four times trigger should be fired and 4 rows should be created in the trigger table
but only one row is created , one row is for ID = 1 Following is the EmpTest script
CREATE TABLE [dbo].[EmpTest] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[Name] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Dept] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO Following is the EmpTrg script CREATE TABLE [dbo].[EmpTrig] (
[ID] [int] NOT NULL ,
[Name] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Dept] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[operation] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[MOd_User] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[datime] [datetime] NULL
) ON [PRIMARY]
GO Following is the Trigger script ALTER trigger trgEmp
on EmpTest
for insert,update,delete
as
declare @Name varchar(50),@Dept varchar(50) if exists(select ‘1’ from inserted)
begin
if update(Name)
begin
select @Name = Name from inserted
end
if update(Dept)
begin
select @Dept = dept from inserted
end
insert into EmpTrig
values(
4,@Name,@dept,’insert’,’Ram’,getdate())
–select *,’insert’,’Ram’,getdate() from deleted
end if exists (select ‘1’ from deleted) and not exists(select ‘1’ from inserted)
begin select @Name = Name from inserted
insert into EmpTrig
select *,’delete’,’Ram’,getdate() from deleted end Thanks in Advance
Regards
Ram

---

your trigger are not handling it in set based
ALTER trigger trgEmp
on EmpTest
for insert,update,delete
as
declare @opr varchar(10) if exists (select * from inserted) select @opr = ‘insert’
if exists (select * from deleted) select @opr = ‘delete’
if exists (select * from inserted)
and exists (select * from deleted) select @opr = ‘update’ insert into EmpTrig (ID, Name, Dept, operation, MOd_User, datime)
select ID, Name, Dept, @opr , system_user, getdate()
from inserted
union all
select ID, Name, Dept, @opr , system_user, getdate()
from deleted KH

---

Hi KH,
thnx for the response.
Can you tell how to handle set based triggers. I am alsao trying parallely.
Thanx in advance
Ram

---

What i posted, handled triggers in set based. Basically you can’t assume trigger will be fired for each row or record that is inserted or updated. The Triggers will fire for more than one records. Which means, the inserted and deleted table may contains more than one records. in your original trigger code, you did not handled this. Example :
KH

---

]]>