dynamic sql in a trigger - SQL Server Forums

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 2000 Forums

SQL Server Development (2000)

dynamic sql in a trigger

Author

Topic

matt_calhoon
Posting Yak Master

235 Posts

Posted - 2002-02-05 : 02:14:31

Hi there,

Ive a mate who is having troubles with a trigger. He needs to pass a column name to the update statement within a trigger. I suggested this:

ALTER TRIGGER tr_Totals
ON dbo.Main
FOR UPDATE,INSERT
AS

DECLARE @SQL varchar(1000)
-- If Status Not Changed then Exit
IF NOT UPDATE(Status)
RETURN
-- Declare Status Variables
declare @StatusOld int
declare @StatusNew int
declare @PersonOld int
declare @PersonNew int
declare @StatusFieldOld varchar(20)
declare @StatusFieldNew varchar(20)
Declare @SQLOLD VarChar(1000)
Declare @SQLNEW VarChar(1000)
declare @OldCount int
declare @NewCount int

--Get Values For Status Variables

Select @StatusOld = Status FROM deleted
Select @StatusNew = Status FROM inserted
Select @PersonOld = AssigndEID FROM deleted
Select @PersonNew = AssigndEID FROM inserted
Select @StatusFieldOld = Status From Status WHERE ID = @StatusOld
Select @StatusFieldNew = Status From Status WHERE ID = @StatusNew

Begin

SET @SQL = 'Update Totals Set ' + CAST(@StatusOld AS VARCHAR(20)) + ' = (Select ' + CAST(@StatusfieldOld AS VARCHAR(20)) + ' From Totals Where Assigned = ' + CAST(@PersonOld AS VARCHAR(20)) + ') -1'
SET @SQL = @SQL + ' WHERE Assigned = ' + CAST(@PersonOld AS VARCHAR(20))
SET @SQL = @SQL + 'Update Totals Set ' + CAST(@StatusNew AS VARCHAR(20)) + ' = (Select ' + CAST(@StatusfieldNew AS VARCHAR(20)) + ' From Totals Where Assigned = ' + CAST(@PersonNew AS VARCHAR(20)) + ') +1'
SET @SQL = @SQL + ' WHERE Assigned = ' + CAST(@PersonNew AS VARCHAR(20))

EXEC @SQL
End

but he gets errors saying "could not find stored procedure 'update Totals...etc etc"

The original Trigger he had was this (which didnt work):

ALTER TRIGGER tr_Totals
ON dbo.Main
FOR UPDATE,INSERT
AS

-- If Status Not Changed then Exit

IF NOT UPDATE(Status)
RETURN
-- Declare Status Variables

declare @StatusOld int
declare @StatusNew int
declare @PersonOld int
declare @PersonNew int
declare @StatusFieldOld varchar(20)
declare @StatusFieldNew varchar(20)
Declare @SQLOLD VarChar(1000)
Declare @SQLNEW VarChar(1000)
declare @OldCount int
declare @NewCount int

--Get Values For Status Variables
Select @StatusOld = Status FROM deleted
Select @StatusNew = Status FROM inserted
Select @PersonOld = AssigndEID FROM deleted
Select @PersonNew = AssigndEID FROM inserted
Select @StatusFieldOld = Status From Status WHERE ID = @StatusOld
Select @StatusFieldNew = Status From Status WHERE ID = @StatusNew

Begin
Update Totals
Set @StatusOld = (Select @StatusfieldOld From Totals Where Assigned = @PersonOld) -1
WHERE Assigned = @PersonOld

Update Totals

Set @StatusNew = (Select @StatusfieldNew From Totals Where Assigned = @PersonNew) +1

WHERE Assigned = @PersonNew

End

Is it possible to have dynamic sql in a trigger?

cheers - matto

Nazim
A custom title

1408 Posts

Posted - 2002-02-05 : 02:45:00

Matt, you can try creating a stored procedure with dynamic sql and passing the parameters from the trigger.

HTH

--------------------------------------------------------------
Dont Tell God how big your Problem is , Tell the Problem how Big your God is

Edited by - Nazim on 02/10/2002 01:21:40

matt_calhoon
Posting Yak Master

235 Posts

Posted - 2002-02-05 : 02:58:50

Hi Nazim,

thank you for your reply.

I have previously tried what you recommended with;

ALTER TRIGGER tr_Totals
ON dbo.Main
FOR UPDATE,INSERT
AS

-- If Status Not Changed then Exit

IF NOT UPDATE(Status)
RETURN
-- Declare Status Variables

declare @StatusOld int
declare @StatusNew int
declare @PersonOld int
declare @PersonNew int
declare @StatusFieldOld varchar(20)
declare @StatusFieldNew varchar(20)
Declare @SQLOLD VarChar(1000)
Declare @SQLNEW VarChar(1000)
declare @OldCount int
declare @NewCount int

--Get Values For Status Variables

Select @StatusOld = Status FROM deleted
Select @StatusNew = Status FROM inserted
Select @PersonOld = AssigndEID FROM deleted
Select @PersonNew = AssigndEID FROM inserted
Select @StatusFieldOld = Status From Status WHERE ID = @StatusOld
Select @StatusFieldNew = Status From Status WHERE ID = @StatusNew

Begin

Update Totals

Set @StatusOld = (exec sp_Test @Column = @StatusFieldOld, @Person = @PersonOld) -1

WHERE Assigned = @PersonOld

Update Totals

Set @StatusNew = (exec sp_Test @Column = @StatusFieldNew, @Person = @PersonNew) +1

WHERE Assigned = @PersonNew

End

but I get errors;
Incorrect syntax near the keyword 'exec'.

hande
Starting Member

8 Posts

Posted - 2002-02-05 : 03:08:09

Well, We had one stupid not even novice, but he was so stupid allthough the company was announced new local office in Down Under.

He tried to do everything too fast considering not think (little) before pushing buttons to get server stucked.

Maybe he thought that it is same principal as webbisite scripting in db_server.

-- -- * * * * * * * * * * * * * * * -- --

matt_calhoon
Posting Yak Master

235 Posts

Posted - 2002-02-05 : 03:11:53

maybe if I read that last post backwards it would make sense...

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-02-05 : 03:21:26

Or if you read every other word...

Or, every other word, backwards...

Hell, can't make LESS sense than the original!

Nazim
A custom title

1408 Posts

Posted - 2002-02-05 : 03:40:58

leave hande on his own. either hez too intelligent or too foolish.

coming to your question matt.

ALTER TRIGGER tr_Totals
ON dbo.Main
FOR UPDATE,INSERT
AS

Set nocount on
DECLARE @SQL varchar(1000)
-- If Status Not Changed then Exit
IF NOT UPDATE(Status)
RETURN
-- Declare Status Variables
declare @StatusOld int
declare @StatusNew int
declare @PersonOld int
declare @PersonNew int
declare @StatusFieldOld varchar(20)
declare @StatusFieldNew varchar(20)
Declare @SQLOLD VarChar(1000)
Declare @SQLNEW VarChar(1000)
declare @OldCount int
declare @NewCount int

--Get Values For Status Variables

Select @StatusOld = Status FROM deleted
Select @StatusNew = Status FROM inserted
Select @PersonOld = AssigndEID FROM deleted
Select @PersonNew = AssigndEID FROM inserted
Select @StatusFieldOld = Status From Status WHERE ID = @StatusOld
Select @StatusFieldNew = Status From Status WHERE ID = @StatusNew

Begin

SET @SQL = 'Update Totals Set ' + CAST(@StatusOld AS VARCHAR(20)) + ' = (Select ' + CAST(@StatusfieldOld AS VARCHAR(20)) + ' From Totals Where Assigned = ' + CAST(@PersonOld AS VARCHAR(20)) + ') -1'
SET @SQL = @SQL + ' WHERE Assigned = ' + CAST(@PersonOld AS VARCHAR(20))
SET @SQL = @SQL + 'Update Totals Set ' + CAST(@StatusNew AS VARCHAR(20)) + ' = (Select ' + CAST(@StatusfieldNew AS VARCHAR(20)) + ' From Totals Where Assigned = ' + CAST(@PersonNew AS VARCHAR(20)) + ') +1'
SET @SQL = @SQL + ' WHERE Assigned = ' + CAST(@PersonNew AS VARCHAR(20))

EXEC test_proc @sql
End

---Stored Procedure here
create procedure test_proc(@ssql varchar(1000))
as
set nocount on
begin
exec @ssql
End

HTH

--------------------------------------------------------------
Dont Tell God how big your Problem is , Tell the Problem how Big your God is

Nazim
A custom title

1408 Posts

Posted - 2002-02-10 : 01:24:22

Matt , you can use Dynamic Sql with Triggers. Am sorry for my earlier post in which i indicated otherwise i have edit it now.

your trigger works fine. the only flaw it has is. instead of using
exec(@Sql) ,you wrote exec @sql which will not work.

ALTER TRIGGER tr_Totals
ON dbo.Main
FOR UPDATE,INSERT
AS

DECLARE @SQL varchar(1000)
-- If Status Not Changed then Exit
IF NOT UPDATE(Status)
RETURN
-- Declare Status Variables
declare @StatusOld int
declare @StatusNew int
declare @PersonOld int
declare @PersonNew int
declare @StatusFieldOld varchar(20)
declare @StatusFieldNew varchar(20)
Declare @SQLOLD VarChar(1000)
Declare @SQLNEW VarChar(1000)
declare @OldCount int
declare @NewCount int

--Get Values For Status Variables

Select @StatusOld = Status FROM deleted
Select @StatusNew = Status FROM inserted
Select @PersonOld = AssigndEID FROM deleted
Select @PersonNew = AssigndEID FROM inserted
Select @StatusFieldOld = Status From Status WHERE ID = @StatusOld
Select @StatusFieldNew = Status From Status WHERE ID = @StatusNew

Begin

SET @SQL = 'Update Totals Set ' + CAST(@StatusOld AS VARCHAR(20)) + ' = (Select ' + CAST(@StatusfieldOld AS VARCHAR(20)) + ' From Totals Where Assigned = ' + CAST(@PersonOld AS VARCHAR(20)) + ') -1'
SET @SQL = @SQL + ' WHERE Assigned = ' + CAST(@PersonOld AS VARCHAR(20))
SET @SQL = @SQL + 'Update Totals Set ' + CAST(@StatusNew AS VARCHAR(20)) + ' = (Select ' + CAST(@StatusfieldNew AS VARCHAR(20)) + ' From Totals Where Assigned = ' + CAST(@PersonNew AS VARCHAR(20)) + ') +1'
SET @SQL = @SQL + ' WHERE Assigned = ' + CAST(@PersonNew AS VARCHAR(20))

EXEC(@SQL)
End

--------------------------------------------------------------
Dont Tell God how big your Problem is , Tell the Problem how Big your God is

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2002-02-10 : 04:01:52

Hande, if you post in Finnish we may be able to understand you better.

matt_calhoon
Posting Yak Master

235 Posts

Posted - 2002-02-11 : 01:06:08

Thanks Nazim,

this works fine now.

cheers - matt

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources