deleted trigger moving data from one table to othe

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

Transact-SQL (2005)

deleted trigger moving data from one table to othe

Author

Topic

m1st
Starting Member

5 Posts

Posted - 2008-02-22 : 04:55:38

I wrote a trigger. I want to generate fields and values dynamically. With fields all ok, but with values a problem.
The trigger:

ALTER TRIGGER [tr_del]
ON [dbo].[module_sectors]
AFTER DELETE
AS
BEGIN
DECLARE @q nVARCHAR(1000), @value nVARCHAR(MAX), @title VARCHAR(100), @tb VARCHAR(100)
set @title = 'title' // for example, dynamic field
select @value = (SELECT title FROM deleted) // here all work fine, but this is not dynamically
set @q = N'SELECT @value = ['+@title+'] FROM deleted'
EXEC SP_EXECUTESQL @q,N'@value nVARCHAR(MAX) OUTPUT', @value OUTPUT
// here dynamically, but when I try to delete row I get error:
No rows were deleted.
A problem occurred attempting to delete row 18.
Error Source: .Net SqlClient Data Provider.
Error Message: Invalid object name 'Deleted'.
// The variant select @value = (SELECT @title FROM deleted) doesn't work
variable @title has value title
set @q = 'INSERT INTO trash_test ( here field )VALUES('''+@value+''')'
exec(@q)
END
Who can help me to solve this problem? I just want to move data from one table to another dynamically with delete trigger. Thanks.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-22 : 06:25:54

One reason for error may be that you havent defined the parameter @title within SP_EXECUTESQL statement. I really cant understand what you are trying to do here. can you explain what you are trying to do?

m1st
Starting Member

5 Posts

Posted - 2008-02-22 : 06:38:15

quote:
Originally posted by visakh16

One reason for error may be that you havent defined the parameter @title within SP_EXECUTESQL statement. I really cant understand what you are trying to do here. can you explain what you are trying to do?

I want to move deleted data to other table.
I think I can do it whith query:
set @q = 'INSERT INTO finhill.trash_test select * from deleted'
exec(@q)
But error:
No rows were deleted.
A problem occurred attempting to delete row 18.
Error Source: .Net SqlClient Data Provider.
Error Message: Invalid object name 'Deleted'.

One person said that exec opens another transaction which is outside trigger.
I tried:
ALTER TRIGGER [tr_del]
ON [dbo].[module_sectors]
FOR DELETE
AS
BEGIN
DECLARE @q nVARCHAR(1000), @value nVARCHAR(MAX), @title VARCHAR(100), @tb VARCHAR(100)

set @q = 'INSERT INTO finhill.trash_test select * from deleted'
BEGIN DISTRIBUTED TRANSACTION;
exec(@q)
COMMIT TRANSACTION;
END
Again error and now I dont no what to do.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-02-22 : 06:43:08

why do are you using dynamic sql in trigger ? exec() execute in a separate context from your current so the deleted will not be available.

Can't you just do it like


create trigger tr_del on module_sectors for delete
as
begin
    insert into finhill.trash_text ( <specify the column name here> )
    select <specify the column name here>
    from   deleted
end

KH
[spoiler]Time is always against us[/spoiler]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-22 : 06:45:14

do you have any specific reason why you went for dynamic query? I think you need just a simple insert statement as khtan pointed out.

m1st
Starting Member

5 Posts

Posted - 2008-02-22 : 07:23:40

I have ~120 columns in the table. if I will do
insert into finhill.trash_text ( <specify the column name here> )
select <specify the column name here>
from deleted
I need copy this 120 times. Is it good? If in future I will change count of the fields I will need to edit trigger.
But I can do it dynamically example
DECLARE get_fieldname CURSOR FOR
SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE (TABLE_NAME = 'module_notices')
OPEN get_fieldname
FETCH NEXT FROM get_fieldname
then WHILE and query generation. At the end exec insert query and thats all.
Maybe you know solution?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-22 : 08:02:38

quote:
Originally posted by m1st

I have ~120 columns in the table. if I will do
insert into finhill.trash_text ( <specify the column name here> )
select <specify the column name here>
from deleted
I need copy this 120 times. Is it good? If in future I will change count of the fields I will need to edit trigger.
But I can do it dynamically example
DECLARE get_fieldname CURSOR FOR
SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE (TABLE_NAME = 'module_notices')
OPEN get_fieldname
FETCH NEXT FROM get_fieldname
then WHILE and query generation. At the end exec insert query and thats all.
Maybe you know solution?

why should you copy it? you can use

INSERT INTO finhill.trash_text ( field1,field2,...field120)
SELECT fieldval1,fieldval2,...fieldval120
FROM DELETED

whenever you add new columns you do need to edit trigger. i dont think its that much of a pain. Its not at all advisable to use dynamic queries in your trigger.

m1st
Starting Member

5 Posts

Posted - 2008-02-22 : 08:32:42

visakh16, there is no opportunity to do this dynamically only static query?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-02-22 : 09:02:26

quote:
Originally posted by m1st

visakh16, there is no opportunity to do this dynamically only static query?

Maybe if you can try to explain to us why do you need dynamic SQL in trigger ?

KH
[spoiler]Time is always against us[/spoiler]

m1st
Starting Member

5 Posts

Posted - 2008-02-22 : 09:29:27

It is wrong to do static query in my situation. Imagine, I have ~120 fields and will be much more. Trigger's code will be terrible. Otherwise, I can make error(s) because there are many fields. It is a headache... But if there is solution to make dynamic query, why not? I don't need to do it, system will do it for me. And there are no more problems and pain. :)
I can do it with PHP language after delete query, but with this database people will work not only within the web, but with C# application. I found some articles where people write how to make dynamic query, but I work with virtual table and there is a problem with transaction.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-02-22 : 09:55:35

quote:
It is wrong to do static query in my situation. Imagine, I have ~120 fields and will be much more. Trigger code will be terrible

The trigger code will for dynamic sql will definitely not look good. How many statement you will need to write the dynamic SQL code ? for the Static query, it is only one single statement. Clean and plain and no room for any error even with 120 fields. If you are worrying about making mistake while typing in the 120 fields, the QA or SSMS can give you a hand on this.

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources