A trigger that backups data from several tables...?

Hi,
I have a problem... I want to backup data to a backuptable from a table
that has a relation to an another table. And the backup should be done
with a trigger when an update or delete occurs... What I mean is that
if I have a table A with columns of NameID, Name and LinkID... then the
LinkID column has a reference to another table B, which has columns of
LinkID, LinkName. I know how to get a backup from table A or Table B...
but I don't know how to get a backup from both of the tables at the
same time to a single table that would consist of the following
information: NameID, Name, LinkID and LinkName. (Should there be a use
of joins in the trigger or how do I get all the required info?)
Table A
======
_____________________
| NameID | Name | LinkID |
---
| 1 | Billy | 1 |
Table B
======
_________________
| LinkID | LinkName |
--
| 1 | blaa blaa |
.... and what I want in the backuptable:
_______________________________
| NameID | Name | LinkID | LinkName |
----
| 1 | Billy | 1 | blaa blaa |
Thanx in advance!Here is an example of an audit trigger I use:
CREATE trigger EmployeeAudit on Employee
AFTER INSERT, UPDATE, DELETE
AS
insert into EmployeeAudit select getdate(), 'D', * from deleted
insert into EmployeeAudit select getdate(), 'I', * from inserted
GO
You can also join the [deleted] or [inserted] tables with other tables like
so:
insert into EmployeeAudit
select
deleted.a,
deleted.b,
TableB.c,
TableB.d
from deleted
join TableB
on TableB.EmployeeID = deleted.EmployeeID
However, if TableB is a code table and you just want to include descriptions
in the audit, then perhaps you could implement the audit table with the same
structure as the updated table, and then implement a view that joins the
audit table with the related table.
"patte" <fipatte@.luukku.com> wrote in message
news:1138044677.419112.215820@.g44g2000cwa.googlegroups.com...
> Hi,
> I have a problem... I want to backup data to a backuptable from a table
> that has a relation to an another table. And the backup should be done
> with a trigger when an update or delete occurs... What I mean is that
> if I have a table A with columns of NameID, Name and LinkID... then the
> LinkID column has a reference to another table B, which has columns of
> LinkID, LinkName. I know how to get a backup from table A or Table B...
> but I don't know how to get a backup from both of the tables at the
> same time to a single table that would consist of the following
> information: NameID, Name, LinkID and LinkName. (Should there be a use
> of joins in the trigger or how do I get all the required info?)
> Table A
> ======
> _____________________
> | NameID | Name | LinkID |
> ---
> | 1 | Billy | 1 |
> Table B
> ======
> _________________
> | LinkID | LinkName |
> --
> | 1 | blaa blaa |
>
> .... and what I want in the backuptable:
> _______________________________
> | NameID | Name | LinkID | LinkName |
> ----
> | 1 | Billy | 1 | blaa blaa |
>
> Thanx in advance!
>|||thanks!!! :)
...I got it to work just as I wanted to.|||Keep in mind NOT to use the asteriks if you are goind in productional
code. Especially in triggers that can cause a tremendous trouble. If
the schema is modified in some way (lets say at the source table of the
trigger and you didn=B4t extend the schema at the backup table, every
transaction made on the source table will fail. better use named
columns in here.
HTH, Jens Suessmeyer.