A trigger to clone a table?

When inserted/update the contents of Table A should go to Table B. I plan on making Table B the history of Table A, so, rows in Table B should not be deleted when they're deleted in Table A. The concept is easy, 'History'.

How can I achieve this?

Please illustrate with an example.

Thanks.
John86aAsked:
Who is Participating?
 
LowfatspreadConnect With a Mentor Commented:
first create a history table for the table to be audited...

this should have all the columns of the source table with the addition of the follwoing columns

historypk bigint identity(1,1)  - primary key for the table
timeofevent datetime
eventtype char(1) -- to indicate Insert/update/delete

then 3 triggers to perform the required inserts....

pk refers to the primary key of the source table...
create trigger trg_I_hist_tableA
for insert
begin
  insert into tableb
    (timeofevent,eventtype, listoftablecolumns)
    select getdate(),'I'
         ,listoftablecolumns
      from inserted

end

create trigger trg_U_hist_tableA
for update
begin
 insert into tableb
    (timeofevent,eventtype, listoftablecolumns)
    select getdate()
          ,case when i.pk is null then 'X' -- delete via update
                when d.pk is null then 'A' -- insert via update
                else 'U'
                end
  -- then for each column
      ,case when i.pk is null then d.columnname
            else i.columnname
            end
      from inserted as I
      full outer join deleted as d
        on I.pk=d.pk
      where i.pk is null
         or d.pk is null
         or Not (    (i.col1 = d.col1 or (i.col1 is null and d.col1 is null) )
                 and (i.col2 = d.col2 or (i.col2 is null and d.col2 is null) )
                 and ...
                 and (i.coln = d.coln or (i.coln is null and d.coln is null) )
                )

end

create trigger trg_D_hist_tableA
for Delete
begin
  insert into tableb
    (timeofevent,eventtype, listoftablecolumns)
    select getdate(),'D'
         ,listoftablecolumns
      from Deleted

end

Open in new window

0
 
Imran Javed ZiaConsultant Software Engineer - .NET ArchitectCommented:
Hi,
Just use After Insert trigger on Table A
and when you are in tigger insert the row in Table B

CREATE TRIGGER [ schema_name . ]trigger_name
ON { table | view }
[ WITH <dml_trigger_option> [ ,...n ] ]
{ FOR | AFTER | INSTEAD OF }
{ [ INSERT ] [ , ] [ UPDATE ] [ , ] [ DELETE ] }
[ WITH APPEND ]
[ NOT FOR REPLICATION ]
AS { sql_statement  [ ; ] [ ,...n ] | EXTERNAL NAME <method specifier [ ; ] > }

<dml_trigger_option> ::=
    [ ENCRYPTION ]
    [ EXECUTE AS Clause ]

<method_specifier> ::=
    assembly_name.class_name.method_name

Trigger on a CREATE, ALTER, DROP, GRANT, DENY, REVOKE, or UPDATE STATISTICS statement (DDL Trigger)
CREATE TRIGGER trigger_name
ON { ALL SERVER | DATABASE }
[ WITH <ddl_trigger_option> [ ,...n ] ]
{ FOR | AFTER } { event_type | event_group } [ ,...n ]
AS { sql_statement  [ ; ] [ ,...n ] | EXTERNAL NAME < method specifier >  [ ; ] }

<ddl_trigger_option> ::=
    [ ENCRYPTION ]
    [ EXECUTE AS Clause ]

<method_specifier> ::=
    assembly_name.class_name.method_name

Trigger on a LOGON event (Logon Trigger)
CREATE TRIGGER trigger_name
ON ALL SERVER
[ WITH <logon_trigger_option> [ ,...n ] ]
{ FOR| AFTER } LOGON  
AS { sql_statement  [ ; ] [ ,...n ] | EXTERNAL NAME < method specifier >  [ ; ] }

<logon_trigger_option> ::=
    [ ENCRYPTION ]
    [ EXECUTE AS Clause ]

<method_specifier> ::=
    assembly_name.class_name.method_name
 

please follow the links for detail and example:
http://www.kodyaz.com/articles/sql-trigger-example-in-sql-server-2008.aspx
http://msdn.microsoft.com/en-us/library/ms189799.aspx


Thanks
0
 
LowfatspreadCommented:
you may also like to consider using the audit table feature of sql server...

CDC - Change data capture may also be of use...

similarly you could use replication (to another database i'd suggest)
where you would ignore deletions , and write a custom stored procedure to wirte a delete row... you'd still need to create the tables  with the additional columns and write customised procedures.
0
 
gladfellowCommented:
Without triggers, you may want to consider an alternative for implementing a simple 'History' requirement.

Have only one table in which you insert, update and SOFT-DELETE. By SOFT-DELETE, I mean do not delete any row, but simply mark it as 'Deleted' using an extra column. This would be the primary table as well as your audit table. However, for all your active query needs, create a view that 'SELECTS FROM THE PRIMARY TABLE WHERE RECORD STATUS IS NOT 'Deleted''.

This may satisfy a simple requirement without having to overload each insert and update with corresponding triggers to clone those inserts and updates.

- Venkat
0
 
John86aAuthor Commented:
Thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.