September 1, 2010 at 2:04 am
Here is the trigger in which i am getting error. This trigger automatically generated for developement rollout so i think i cant make any changes in that trigger. I just have to paste that trigger into development.
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.
Looking forward to hear from expert guys.
Trigger:
-- 12:50:15 PM
-- Add Update Trigger to TestSmorgasbord
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
-- 12:50:15 PM
-- Add Delete Trigger to TestSmorgasbord
Create Trigger TR_TestSmorgasbord_D on TestSmorgasbord for Delete 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()
if @UserName != 'systrans' and @UserName != 'dmztrans'
BEGIN
RAISERROR( 'Invalid attempt to physically delete a row', 16, -1 )
ROLLBACK TRANSACTION
END
--RAISERROR( 'Invalid attempt to physically delete a row', 16, -1 )
go
September 1, 2010 at 2:46 am
Sorry, there are two triggers
Here is the one i am getting error.
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
September 1, 2010 at 2:53 am
Anyway i go the solution, i have declare variable twice.
Thanks a lot
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply