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  SQL Server Administration (2008)  Trigger Error- Variable must declare

Author  Topic  Easyboy Starting Member 2 Posts Posted - 2010-09-01 : 04:34:07 This is the error while creating trigger. Error: Error: Msg 134, Level 15, State 1, Procedure TR_TestSmorgasbord_U, Line 113 The variable name '@req' has already been declared. Variable names must be unique within a query batch or stored procedure. Trigger: Create Trigger TR_TestSmorgasbord_U on TestSmorgasbord for Update NOT FOR REPLICATION as if (@@RowCount = 0) return declare @AtDateTime datetime, @LogOperation tinyint, @UserName varchar(255) declare @AppName varchar(255) SET NOCOUNT ON /* ** Retrieve current user name executing the SQL action */ SELECT @UserName = loginame FROM master.dbo.sysprocesses WHERE spid=@@SPID /* ** Retrieve the name of the current application */ select @AppName = APP_NAME() if (len(@AppName) = 0) SELECT @AppName = 'Unknown' /* ** Retrieve default log data */ select @AtDateTime = GETDATE() /* ** OID Key Protection for TestSmorgasbord */ if Update(KeyTestSmorgasbord) Begin RaisError( 'Invalid attempt to update OID KeyTestSmorgasbord in TestSmorgasbord', 16, 1 ) Rollback Tran return end /* **Unique Constraint for TestSmorgasbord - TestGenericItem1.TestDate. */ if (Update(TestGenericItem1) or Update(TestDate)) and Exists( SELECT * FROM inserted i INNER LOOP JOIN TestSmorgasbord x ON (i.TestGenericItem1=x.TestGenericItem1 and i.TestDate=x.TestDate) WHERE i.updoperation IN (0, 1) AND x.updoperation IN (0, 1) GROUP BY x.TestGenericItem1,x.TestDate HAVING COUNT(*) > 1) BEGIN RAISERROR( 'Invalid attempt to enter duplicate TestGenericItem1,TestDate in TestSmorgasbord', 16, -1 ) ROLLBACK TRAN RETURN END if @UserName = 'systrans' or @UserName = 'dmztrans' return set @LogOperation = 1 --Begin Custom Trigger --Begin MRS Trigger Code declare @req int, @holder int, @bindtoken varchar(255), @hr int declare @TestGenericItem1 varchar( 50 ) if exists (select * from deleted where updoperation in (0,1)) or exists (select * from inserted where updoperation in (0,1)) begin exec sp_OACreate 'SNL.TriggerRequest', @req OUTPUT exec sp_OAGetProperty @req, 'KeyHolder', @holder OUTPUT, '{928f1d60-783d-11d3-91f6-00c04f2e3ed7}', 0, 2790 declare curs cursor for select distinct TestGenericItem1 from inserted where updoperation in (0,1) union select distinct TestGenericItem1 from deleted where updoperation in (0,1) open curs fetch next from curs into @TestGenericItem1 while @@FETCH_STATUS = 0 begin exec @hr = sp_OAMethod @holder, 'AddKeys', NULL, @TestGenericItem1 if @hr <> 0 begin exec sp_OADestroy @holder exec sp_OADestroy @req close curs deallocate curs raiserror( 'Trigger error, phase 1, return code %x', 16, -1, @hr ) rollback tran return end fetch next from curs into @TestGenericItem1 end exec sp_OADestroy @holder close curs deallocate curs end if exists (select * from deleted where updoperation in (0,1)) or exists (select * from inserted where updoperation in (0,1)) begin exec sp_getbindtoken @bindtoken OUTPUT, 1 exec @hr = sp_OAMethod @req, 'Execute', NULL, @bindtoken, -1 exec sp_OADestroy @req if @hr <> 0 begin raiserror( 'Trigger error, phase 2, return code %x', 16, -1, @hr ) rollback tran return end end --End MRS Trigger Code --Begin RowParentIndicator Trigger Code if update(KeyTestSmorgasbordParent) begin declare @req int, @holder int, @bindtoken varchar(255), @hr int declare @TreeID int exec sp_OACreate 'SNL.TriggerRequest', @req OUTPUT exec sp_OAGetProperty @req, 'KeyHolder', @holder OUTPUT, '{928f1e95-783d-11d3-91f6-00c04f2e3ed7}', 0, 2790 declare curs cursor for select distinct TreeID from deleted open curs fetch next from curs into @TreeID while @@FETCH_STATUS = 0 begin exec @hr = sp_OAMethod @holder, 'AddKeys', NULL, @TreeID if @hr <> 0 begin exec sp_OADestroy @holder exec sp_OADestroy @req close curs deallocate curs raiserror( 'Trigger error, phase 1, return code %x', 16, -1, @hr ) rollback tran return end fetch next from curs into @TreeID end close curs deallocate curs declare curs cursor for select distinct TreeID FROM TestSmorgasbord Where KeyTestSmorgasbord IN ( SELECT DISTINCT KeyTestSmorgasbordParent from inserted ) open curs fetch next from curs into @TreeID while @@FETCH_STATUS = 0 begin exec @hr = sp_OAMethod @holder, 'AddKeys', NULL, @TreeID if @hr <> 0 begin exec sp_OADestroy @holder exec sp_OADestroy @req close curs deallocate curs raiserror( 'Trigger error, phase 1, return code %x', 16, -1, @hr ) rollback tran return end fetch next from curs into @TreeID end close curs deallocate curs declare curs cursor for select distinct KeyTestSmorgasbord FROM inserted WHERE KeyTestSmorgasbordParent IS NULL open curs fetch next from curs into @TreeID while @@FETCH_STATUS = 0 begin exec @hr = sp_OAMethod @holder, 'AddKeys', NULL, @TreeID if @hr <> 0 begin exec sp_OADestroy @holder exec sp_OADestroy @req close curs deallocate curs raiserror( 'Trigger error, phase 1, return code %x', 16, -1, @hr ) rollback tran return end fetch next from curs into @TreeID end close curs deallocate curs exec sp_OADestroy @holder exec sp_getbindtoken @bindtoken OUTPUT, 1 exec @hr = sp_OAMethod @req, 'Execute', NULL, @bindtoken, -1 exec sp_OADestroy @req if @hr <> 0 begin raiserror( 'Trigger error, phase 2, return code %x', 16, -1, @hr ) rollback tran return end end --End RowParentIndicator Trigger Code --End Custom Trigger /* ** UpdDate Check */ if not Update(UpdDate) begin Update a set UpdDate = @AtDateTime from TestSmorgasbord a, Inserted i where a.KeyTestSmorgasbord = i.KeyTestSmorgasbord if @@ERROR<>0 begin RollBack tran return/* Execution stops here! */ end end /* ** Log the change --New Logging Logic -Omar Nov2008 */ INSERT INTO SNLEditLog_new.dbo.TestSmorgasbord (KeyTestSmorgasbord, WhenModified, KeyItem, UserName, AppName, UpdOperation, OldValue, NewValue) SELECT * FROM (SELECT i.KeyTestSmorgasbord, @AtDateTime AS WhenModified, KeyItem_, @UserName AS UserName, @AppName AS AppName, @LogOperation AS UpdOperation, CASE KeyItem_ WHEN N'52052' THEN convert(varChar(255),d.KeyTestSmorgasbordParent) WHEN N'52050' THEN convert(varChar(255),d.TestDate) WHEN N'52054' THEN convert(varChar(255),d.TestFormOrder) WHEN N'51914' THEN convert(varChar(255),d.TestGenericItem1) WHEN N'2082' THEN convert(varChar(255),d.AppStatus) WHEN N'482' THEN convert(varChar(255),d.UpdOperation) END AS OldValue, CASE KeyItem_ WHEN N'52052' THEN convert(varChar(255),i.KeyTestSmorgasbordParent) WHEN N'52050' THEN convert(varChar(255),i.TestDate) WHEN N'52054' THEN convert(varChar(255),i.TestFormOrder) WHEN N'51914' THEN convert(varChar(255),i.TestGenericItem1) WHEN N'2082' THEN convert(varChar(255),i.AppStatus) WHEN N'482' THEN convert(varChar(255),i.UpdOperation) END AS NewValue FROM inserted AS i JOIN deleted AS d ON i.KeyTestSmorgasbord = d.KeyTestSmorgasbord CROSS JOIN (SELECT N'52052' AS KeyItem_ UNION ALL SELECT N'52050' UNION ALL SELECT N'52054' UNION ALL SELECT N'51914' UNION ALL SELECT N'2082' UNION ALL SELECT N'482') AS C) AS T WHERE OldValue <> NewValue OR (OldValue IS NULL AND NewValue IS NOT NULL) OR (OldValue IS NOT NULL AND NewValue IS NULL) go Thanks & Regard