Need help with delete trigger

[m1st]

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:
[code=sql]
ALTER TRIGGER [BackUpNotice]
ON [dbo].[module_notices]
FOR DELETE
AS
BEGIN
DECLARE @fieldname VARCHAR(100)
DECLARE @val VARCHAR(100)
DECLARE @b VARCHAR(MAX)
DECLARE get_fieldname CURSOR FOR
SELECT COLUMN_NAME FROM INFORMATION_SCH EMA.COLUMNS
WHERE (TABLE_NAME = 'module_notices ')

OPEN get_fieldname

FETCH NEXT FROM get_fieldname

WHILE @@FETCH_STATUS = 0
BEGIN
@val = @fieldname + ', ' + @val why this doesnt work? How can I generate fields and values?
FETCH NEXT FROM get_fieldname INTO @fieldname
--set @b = 'SELECT [' + @fieldname + '] FROM Deleted'
--here I need to have result from the query in variable, but I don't no how


END

CLOSE get_fieldname
DEALLOCATE get_fieldname
END [/code]
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.

[debasisdas]

What do you mean by dynamic ?

Are you trying to pass tablename dynamically ?

[m1st]

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)'

[m1st]

Now I have

ALTER TRIGGER [BackUpNotice] 
   ON  [dbo].[module_notices]
   FOR DELETE
AS 
BEGIN
	DECLARE @fieldname nVARCHAR(100), @value nVARCHAR(MAX), @fields nVARCHAR(2000), @values nVARCHAR(2000), @q nVARCHAR(4000)
	--DECLARE @t nVARCHAR(MAX)
	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
	set @fields = ''
	set @values = ''
	set @value = ''
	set @fieldname = ''
	WHILE @@FETCH_STATUS = 0
	BEGIN
		FETCH NEXT FROM get_fieldname INTO @fieldname
		set @q = N'SELECT @value = ['+@fieldname+'] FROM dbo.module_notices WHERE id=3'
		EXEC SP_EXECUTESQL @q,N'@value nVARCHAR(MAX) OUTPUT', @value OUTPUT
		set @value = @value
		set @values = @values + '' + @value + ','
		print @values
		if( @fieldname <> '' )
		BEGIN
			set @fields = @fields + '['+@fieldname + '],'	
		END
	END
	CLOSE get_fieldname
	DEALLOCATE get_fieldname
	select @values
	set @fields = STUFF(@fields,1, 1,'')
	set @q = 'INSERT INTO deleted_notices ('+ @fields +') VALUES()'
	select @q
END

But why variable values is empty?

[ck9663]

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

[m1st]

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.

[ck9663]

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

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

[m1st]

I have finished it.

ALTER TRIGGER [BackUpNotice] 
   ON  [dbo].[module_notices]
   AFTER DELETE
AS 
BEGIN
	SET NOCOUNT ON
	DECLARE @fieldname nVARCHAR(100), @value nVARCHAR(MAX), @fields nVARCHAR(2000), @values nVARCHAR(2000), @q nVARCHAR(4000)
	--DECLARE @t nVARCHAR(MAX)
	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
	set @fields = ''
	set @values = ''
	set @value = ''
	set @fieldname = ''
	WHILE @@FETCH_STATUS = 0
	BEGIN
		FETCH NEXT FROM get_fieldname INTO @fieldname
		set @q = N'SELECT @value = ['+@fieldname+'] FROM Deleted'
		EXEC SP_EXECUTESQL @q,N'@value nVARCHAR(MAX) OUTPUT', @value OUTPUT
		if( @value <> '' )
		BEGIN
			set @values = @values + ','''+@value + ''''
			set @fields = @fields + ',['+@fieldname + ']'
		END
	END
	CLOSE get_fieldname
	DEALLOCATE get_fieldname
	set @fields = STUFF(@fields,1, 1,'')
	set @values = STUFF(@values,1, 1,'')
	set @q = 'INSERT INTO deleted_notices ('+ @fields +') VALUES('+@values+')'
	exec(@q)
	select @q
END

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?

[m1st]

Have you tried doing a

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 :

ALTER TRIGGER [BackUpNotice] 
   ON  [dbo].[module_notices]
   AFTER DELETE
AS 
BEGIN
	SET NOCOUNT ON
	DECLARE @fieldname nVARCHAR(100), @value nVARCHAR(MAX), @fields nVARCHAR(2000), @values nVARCHAR(2000), @q nVARCHAR(4000)

	set @q = 'INSERT INTO deleted_notices SELECT * FROM deleted'
	
	exec(@q)
END

Error: Error Message: Invalid object name 'deleted'.

[ck9663]

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

[m1st]

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?

[m1st]

That is fun. If exec starts other transaction then I cant move data whith query

INSERT INTO BackUPTable select * from deleted

and begin transaction cant help...

[ck9663]

try:

ALTER TRIGGER [BackUpNotice] 
   ON  [dbo].[module_notices]
   AFTER DELETE
AS 
BEGIN
	SET NOCOUNT ON

   INSERT into deleted_notices select * from deleted

END

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