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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.