Trigger to handle multi - rows

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Trigger to handle multi - rows

Author

Topic

laddu
Constraint Violating Yak Guru

332 Posts

Posted - 2014-10-21 : 10:41:32

I have created below trigger to start logging the company changes from the table1 into another audit table. It works fine with single row but crashing with identical change with multiple rows. Can you please help me to update the trigger to handle multi-row scenario. Thanks.

GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Company_AuditPeriod]') AND type in (N'U'))
CREATE TABLE [dbo].[Company_AuditPeriod](
[Client] [varchar](25) NOT NULL,
[Period] [varchar](25),
[Table_Name] [varchar](25),
[Field_Name] [varchar](25),
[Old_Value] [varchar](25),
[New_Value] [varchar](25),
[User_ID] [varchar](25) ,
[Last_Update] [datetime],
[agrtid] [bigint] IDENTITY(1,1) NOT NULL,
) ON [PRIMARY]
GO

--create trigger

SET QUOTED_IDENTIFIER ON
GO

CREATE TRIGGER [dbo].[Table1_Update]
ON [dbo].[Table1]
FOR UPDATE
NOT FOR REPLICATION
AS

BEGIN

DECLARE
@status varchar(3),
@user_id varchar(25),
@period varchar(25),
@client varchar(25),
@last_update datetime

DECLARE
@Old_status varchar(3),
@Old_user_id varchar(25),
@Old_period varchar(25),
@Old_client varchar(25)

SELECT
@status = status,
@user_id = user_id,
@period = period,
@client = client,
@last_update = last_update
FROM Inserted

SELECT
@Old_status = status,
@Old_user_id = user_id,
@Old_period = period,
@Old_client = client
FROM Deleted

If @Old_status <> @status
INSERT INTO Company_AuditPeriod
VALUES ( @client, @period, 'Table1', 'period',@old_status, @status, @user_id, @last_update)

END

GO

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-10-21 : 13:26:18

remove all the variables and just have

INSERT INTO Company_AuditPeriod (client, period, [tablename], [updatetype], status, status, user_id, last_update)
select client, period, 'Table1', 'period', status, status, user_id, last_update
from inserted

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources