By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
460,033 Members | 1,230 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 460,033 IT Pros & Developers. It's quick & easy.

Need help with delete trigger

P: 8
I wanna create delete trigger. Idea is when user deletes the record from table trigger moves this record to another table (this table is copy has the same fields, types) But all this I want to do dinamic. I start so:
Expand|Select|Wrap|Line Numbers
  1. ALTER TRIGGER [BackUpNotice] 
  2.    ON  [dbo].[module_notices]
  3.    FOR DELETE
  4. AS 
  5. BEGIN
  6.     DECLARE @fieldname VARCHAR(100)
  7.     DECLARE @val VARCHAR(100)
  8.     DECLARE @b VARCHAR(MAX)
  9.     DECLARE get_fieldname CURSOR FOR
  10.      SELECT COLUMN_NAME FROM  INFORMATION_SCHEMA.COLUMNS
  11.      WHERE (TABLE_NAME = 'module_notices')
  12.  
  13.     OPEN get_fieldname
  14.  
  15.     FETCH NEXT FROM get_fieldname
  16.  
  17.     WHILE @@FETCH_STATUS = 0
  18.     BEGIN
  19. @val = @fieldname + ', ' + @val why this doesnt work? How can I generate fields and values?
  20.         FETCH NEXT FROM get_fieldname INTO @fieldname
  21.         --set @b = 'SELECT [' + @fieldname + '] FROM Deleted'
  22.         --here I need to have result from the query in variable, but I don't no how
  23.  
  24.  
  25.     END
  26.  
  27.     CLOSE get_fieldname
  28.     DEALLOCATE get_fieldname
  29. END 
I want to generate fields and values and get query
INSERT INTO deleted.notices ( here fields ) VALUES ( here values )

Thank you a lot. And sorry form my English.
Feb 21 '08 #1
Share this Question
Share on Google+
12 Replies


debasisdas
Expert 5K+
P: 8,127
What do you mean by dynamic ?

Are you trying to pass tablename dynamically ?
Feb 21 '08 #2

P: 8
What do you mean by dynamic ?

Are you trying to past tablename dynamically ?
Yes. I generated fields set @val = @val + ',['+@fieldname + ']'
but also need values and then I will paste variables in the query like
set @q = 'INSERT INTO my_table ('+@val+') VALUES (here I have problem, I cant get values from table Deleted with field @fieldname)'
Feb 21 '08 #3

P: 8
Now I have
Expand|Select|Wrap|Line Numbers
  1. ALTER TRIGGER [BackUpNotice] 
  2.    ON  [dbo].[module_notices]
  3.    FOR DELETE
  4. AS 
  5. BEGIN
  6.     DECLARE @fieldname nVARCHAR(100), @value nVARCHAR(MAX), @fields nVARCHAR(2000), @values nVARCHAR(2000), @q nVARCHAR(4000)
  7.     --DECLARE @t nVARCHAR(MAX)
  8.     DECLARE get_fieldname CURSOR FOR
  9.      SELECT COLUMN_NAME FROM  INFORMATION_SCHEMA.COLUMNS
  10.      WHERE (TABLE_NAME = 'module_notices')
  11.     OPEN get_fieldname
  12.  
  13.     FETCH NEXT FROM get_fieldname
  14.     set @fields = ''
  15.     set @values = ''
  16.     set @value = ''
  17.     set @fieldname = ''
  18.     WHILE @@FETCH_STATUS = 0
  19.     BEGIN
  20.         FETCH NEXT FROM get_fieldname INTO @fieldname
  21.         set @q = N'SELECT @value = ['+@fieldname+'] FROM dbo.module_notices WHERE id=3'
  22.         EXEC SP_EXECUTESQL @q,N'@value nVARCHAR(MAX) OUTPUT', @value OUTPUT
  23.         set @value = @value
  24.         set @values = @values + '' + @value + ','
  25.         print @values
  26.         if( @fieldname <> '' )
  27.         BEGIN
  28.             set @fields = @fields + '['+@fieldname + '],'    
  29.         END
  30.     END
  31.     CLOSE get_fieldname
  32.     DEALLOCATE get_fieldname
  33.     select @values
  34.     set @fields = STUFF(@fields,1, 1,'')
  35.     set @q = 'INSERT INTO deleted_notices ('+ @fields +') VALUES()'
  36.     select @q
  37. END
  38.  
But why variable values is empty?
Feb 21 '08 #4

ck9663
Expert 2.5K+
P: 2,878
When you create a trigger, you associate it with a specific table. You can not have a trigger associated with two or more table. You have to create a trigger for the others. It may have exact line of codes, but you have to associate it to a specific table. Which means during creation of this trigger, your columns are already known. May I know why you need to generate the column names dynamically?

I know you may have hundreds of reasons to do this, but there could be other solutions.

-- CK
Feb 21 '08 #5

P: 8
When you create a trigger, you associate it with a specific table. You can not have a trigger associated with two or more table. You have to create a trigger for the others. It may have exact line of codes, but you have to associate it to a specific table. Which means during creation of this trigger, your columns are already known. May I know why you need to generate the column names dynamically?

I know you may have hundreds of reasons to do this, but there could be other solutions.

-- CK
Because I have many fields in the table and I don't want to do this statically.
I found solution here it is:
EXEC SP_EXECUTESQL this procedure will help me to execute query dynamically. I will do some tests.
Feb 22 '08 #6

ck9663
Expert 2.5K+
P: 2,878
Because I have many fields in the table and I don't want to do this statically.
I found solution here it is:
EXEC SP_EXECUTESQL this procedure will help me to execute query dynamically. I will do some tests.

Have you tried doing a

Expand|Select|Wrap|Line Numbers
  1. INSERT INTO BackUPTable select * from deleted 
It will insert the first column on the SELECT to the first column of the BackUpTable, regardless of the name of the columns. Just make sure the sequence, datatype and size of the columns are identical.

-- CK
Feb 22 '08 #7

P: 8
I have finished it.
Expand|Select|Wrap|Line Numbers
  1. ALTER TRIGGER [BackUpNotice] 
  2.    ON  [dbo].[module_notices]
  3.    AFTER DELETE
  4. AS 
  5. BEGIN
  6.     SET NOCOUNT ON
  7.     DECLARE @fieldname nVARCHAR(100), @value nVARCHAR(MAX), @fields nVARCHAR(2000), @values nVARCHAR(2000), @q nVARCHAR(4000)
  8.     --DECLARE @t nVARCHAR(MAX)
  9.     DECLARE get_fieldname CURSOR FOR
  10.      SELECT COLUMN_NAME FROM  INFORMATION_SCHEMA.COLUMNS
  11.      WHERE (TABLE_NAME = 'module_notices')
  12.     OPEN get_fieldname
  13.  
  14.     FETCH NEXT FROM get_fieldname
  15.     set @fields = ''
  16.     set @values = ''
  17.     set @value = ''
  18.     set @fieldname = ''
  19.     WHILE @@FETCH_STATUS = 0
  20.     BEGIN
  21.         FETCH NEXT FROM get_fieldname INTO @fieldname
  22.         set @q = N'SELECT @value = ['+@fieldname+'] FROM Deleted'
  23.         EXEC SP_EXECUTESQL @q,N'@value nVARCHAR(MAX) OUTPUT', @value OUTPUT
  24.         if( @value <> '' )
  25.         BEGIN
  26.             set @values = @values + ','''+@value + ''''
  27.             set @fields = @fields + ',['+@fieldname + ']'
  28.         END
  29.     END
  30.     CLOSE get_fieldname
  31.     DEALLOCATE get_fieldname
  32.     set @fields = STUFF(@fields,1, 1,'')
  33.     set @values = STUFF(@values,1, 1,'')
  34.     set @q = 'INSERT INTO deleted_notices ('+ @fields +') VALUES('+@values+')'
  35.     exec(@q)
  36.     select @q
  37. END
  38.  
But when I delete row in the table 'module_notices' I get error message:
No rows were deleted.
A problem occurred attempting to delete row 18.
Error Source: .Net SqlClient Data Provider.
Error Message: Invalid object name 'Deleted'.
I dont understand how I cant have virtual table Deleted?
Feb 22 '08 #8

P: 8
Have you tried doing a

Expand|Select|Wrap|Line Numbers
  1. INSERT INTO BackUPTable select * from deleted 
It will insert the first column on the SELECT to the first column of the BackUpTable, regardless of the name of the columns. Just make sure the sequence, datatype and size of the columns are identical.

-- CK
Your variant :
Expand|Select|Wrap|Line Numbers
  1. ALTER TRIGGER [BackUpNotice] 
  2.    ON  [dbo].[module_notices]
  3.    AFTER DELETE
  4. AS 
  5. BEGIN
  6.     SET NOCOUNT ON
  7.     DECLARE @fieldname nVARCHAR(100), @value nVARCHAR(MAX), @fields nVARCHAR(2000), @values nVARCHAR(2000), @q nVARCHAR(4000)
  8.  
  9.     set @q = 'INSERT INTO deleted_notices SELECT * FROM deleted'
  10.  
  11.     exec(@q)
  12. END
  13.  
Error: Error Message: Invalid object name 'deleted'.
Feb 22 '08 #9

ck9663
Expert 2.5K+
P: 2,878
It's because the command executed inside the EXEC is not part of your transaction. EXEC opens another transaction which is outside your trigger. Which means the deleted table is now accessible outside the your transaction (in this case, the trigger).

I have not tried it myself, but you might want to check this out.

-- CK
Feb 22 '08 #10

P: 8
It's because the command executed inside the EXEC is not part of your transaction. EXEC opens another transaction which is outside your trigger. Which means the deleted table is now accessible outside the your transaction (in this case, the trigger).

I have not tried it myself, but you might want to check this out.

-- CK
Doesn't help. :/
ck9663, maybe there is another solution. I just need to take deleted data and move it to another table dynamically. I think you should know another way to do this?
Feb 22 '08 #11

P: 8
That is fun. If exec starts other transaction then I cant move data whith query
Expand|Select|Wrap|Line Numbers
  1. INSERT INTO BackUPTable select * from deleted 
and begin transaction cant help...
Feb 22 '08 #12

ck9663
Expert 2.5K+
P: 2,878
try:

Expand|Select|Wrap|Line Numbers
  1. ALTER TRIGGER [BackUpNotice] 
  2.    ON  [dbo].[module_notices]
  3.    AFTER DELETE
  4. AS 
  5. BEGIN
  6.     SET NOCOUNT ON
  7.  
  8.    INSERT into deleted_notices select * from deleted
  9.  
  10. END
  11.  
the catch:
BackUpNotice should be identical in structure with Deleted_Notices

If this is what you want, you might want to implement this in a transaction and do a commit or rollback as necessary.

Happy Coding.

-- CK
Feb 22 '08 #13

Post your reply

Sign in to post your reply or Sign up for a free account.