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:

Expand|Select|Wrap|Line Numbers

 
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_SCHEMA.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

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

Quote:

Originally Posted by debasisdas

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

Expand|Select|Wrap|Line Numbers

 
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

Quote:

Originally Posted by 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

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

Quote:

Originally Posted by m1st

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

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.

Expand|Select|Wrap|Line Numbers

 
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

Quote:

Originally Posted by ck9663

Have you tried doing a

Expand|Select|Wrap|Line Numbers

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

 
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

Quote:

Originally Posted by 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

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

Expand|Select|Wrap|Line Numbers

INSERT INTO BackUPTable select * from deleted

and begin transaction cant help...

ck9663

try:

Expand|Select|Wrap|Line Numbers

 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