User Profile

Collapse

Profile Sidebar

Collapse
m1st
m1st
Last Activity: Mar 3 '08, 10:04 AM
Joined: Feb 21 '08
Location:
  •  
  • Time
  • Show
  • Source
Clear All
new posts

  • m1st
    replied to Need help with delete trigger
    That is fun. If exec starts other transaction then I cant move data whith query
    Code:
    INSERT INTO BackUPTable select * from deleted
    and begin transaction cant help...
    See more | Go to post

    Leave a comment:


  • m1st
    replied to Need help with delete trigger
    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?...
    See more | Go to post

    Leave a comment:


  • m1st
    replied to Need help with delete trigger
    Your variant :
    Code:
    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...
    See more | Go to post

    Leave a comment:


  • m1st
    replied to Need help with delete trigger
    I have finished it.
    Code:
    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
    ...
    See more | Go to post

    Leave a comment:


  • m1st
    replied to Need help with delete trigger
    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....
    See more | Go to post

    Leave a comment:


  • m1st
    replied to Need help with delete trigger
    Now I have
    Code:
    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')
    ...
    See more | Go to post

    Leave a comment:


  • m1st
    replied to Need help with delete trigger
    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)'...
    See more | Go to post

    Leave a comment:


  • m1st
    started a topic Need help with delete trigger

    Need help with delete trigger

    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)...
    See more | Go to post
    Last edited by debasisdas; Feb 21 '08, 01:21 PM. Reason: added code=sql tags
No activity results to display
Show More
Working...