locked
How tto create Trigger after delete on sql table RRS feed

  • Question

  • User2142845853 posted

    Sql server table getting deleted records.  Do not know how its haappening. 

    Trying  to add trigger after delete.    table name is dbo.astr  has 300  columns.  can make another copy like dbo.astrdeleted and anytime there is any delete command, the data is added to this astrdeleted table.    it would be nice to have the trigger send some alert so it can be tracked and isolated to the software causing it.

    all attempts at adding the trigger only generate sql errors.    one example said something like on delete insert * into astrdeleted;  but is this allowed or does it have to be specified literally to say on delete   (logic to match up column?) then insert deleted into (column)

    just want to catch what was deleted and send a message forward

    Friday, December 11, 2015 9:29 PM

Answers

  • User2142845853 posted

    solved, 

    make a backup table using only the column names

    CREATE TABLE table_name
    (
    column_name1 data_type(size),
    column_name2 data_type(size),
    column_name3 data_type(size),
    ....
    );

    get this from sql management studio, right click on the tables name and say Script table As  then  Create Into Table, then create the blank table which becomes the backup, all columns must match (obviously)

    now some text editing, I pasted the raw text into an unused area of a visual studio file, then removed the bracket [, then did replace ] with comma, and got the formatting right and pasted into the t-sql command, execute and it ran with no errors.  

    create trigger  mytrigger1

    on [dbo].[tablename]

    after delete, insert, update

    as

    begin

    set nocount on;

    insert into [dbo].[backuptable]

    ( name1, name2, name3, name4, .......................................... )

    select   name1, name2, name3, name4 ..................... 

    from deleted

    end

    now when anything is changed in the main table of concern, its pushed into this backup table

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, December 14, 2015 4:55 PM

All replies

  • User2142845853 posted
    CREATE TRIGGER [dbo].[mybackup2]
       ON  dbo.Astr
       AFTER delete
    AS 
    BEGIN
    	-- SET NOCOUNT ON added to prevent extra result sets from
    	-- interfering with SELECT statements.
    	SET NOCOUNT ON;
    declare @ID INT
    select @ID = deleted.ID
    from deleted
    insert into dbo.mybackup2
    values(@ID, 'deleted')
    
        -- Insert statements for trigger here
    
    END
    GO
    

    which i got from this site http://www.aspsnippets.com/Articles/Simple-Insert-Update-and-Delete-Triggers-in-SQL-Server-with-example.aspx

    but no matter how I try it always errors out, 

    Msg 213, Level 16, State 1, Procedure mybackup2, Line 17
    Column name or number of supplied values does not match table definition.

    the table has about 300 columns, i found they can be copied, but all i want to do is for ANY delete operation, send that record into the 2nd database table, 

    HOW????

    Friday, December 11, 2015 10:49 PM
  • Monday, December 14, 2015 7:59 AM
  • User2142845853 posted

    ok, there is something wrong with the main table being used.  I can use a different table, make a backup table from it, create a new trigger using the syntax to insert into backup select * from deleted, and it works fine as expected.  the syntax isnt that hard.

    but it worked on the practice table, but totally fails on the table I'm trying to protect, 

    Msg 8101, Level 16, State 1, Procedure deletedhistory, Line 18

    An explicit value for the identity column in table 'mainbackup' can only be specified when a column list is used and IDENTITY_INSERT is ON.

    does another ID column have to be added to the backup table?    tried that.  not allowed.  cant add the column _id and set it to allow nulls = OFF

    'mainbackup' table
    - Unable to modify table.
    Cannot insert the value NULL into column '_id', table 'Asteroiddb.dbo.Tmp_mainbackup'; column does not allow nulls. INSERT fails.
    The statement has been terminated.

    and this is after setting the value to allow changes that require table to be regenerated, Tools-> Options -> Designer -> Table and Database Designers -> Uncheck the box Prevent saving changes that require table re-creation,

    Monday, December 14, 2015 1:36 PM
  • User2142845853 posted

    solved, 

    make a backup table using only the column names

    CREATE TABLE table_name
    (
    column_name1 data_type(size),
    column_name2 data_type(size),
    column_name3 data_type(size),
    ....
    );

    get this from sql management studio, right click on the tables name and say Script table As  then  Create Into Table, then create the blank table which becomes the backup, all columns must match (obviously)

    now some text editing, I pasted the raw text into an unused area of a visual studio file, then removed the bracket [, then did replace ] with comma, and got the formatting right and pasted into the t-sql command, execute and it ran with no errors.  

    create trigger  mytrigger1

    on [dbo].[tablename]

    after delete, insert, update

    as

    begin

    set nocount on;

    insert into [dbo].[backuptable]

    ( name1, name2, name3, name4, .......................................... )

    select   name1, name2, name3, name4 ..................... 

    from deleted

    end

    now when anything is changed in the main table of concern, its pushed into this backup table

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, December 14, 2015 4:55 PM