triggers 2

[gojohnnygogogogo]

hello,
I am trying to add a trigger so when a record is inserted, updated or deleted from one table, it will insert the record into another table.
how can I do something like this.

INSERT INTO Mutations
(AddDate, Prov, Forename, Surname, Sess, SessConf, TermID)
VALUES (GETDATE(), EY.Prov, EY.Forename, EY.Surname, EY.Sess, EY.Sessconf, EY.TermID)

where EY is the table where the insert/update or delete will take place.


thank you.

 

[MeanGreen]

On your EY table create the triggers like this:

CREATE TRIGGER [TRIGGER NAME] ON [dbo].[Ey]
FOR INSERT, UPDATE
AS
INSERT INTO Mutations (AddDate, Prov, Forename, Surname, Sess, SessConf, TermID)
select GETDATE(), ins.Prov, ins.Forename, ins.Surname, ins.Sess, ins.Sessconf, ins.TermID from INSERTED ins

CREATE TRIGGER [TRIGGER NAME] ON [dbo].[Ey]
FOR DELETE
AS
INSERT INTO Mutations (AddDate, Prov, Forename, Surname, Sess, SessConf, TermID)
select GETDATE(), del.Prov, del.Forename, del.Surname, del.Sess, del.Sessconf, del.TermID from deleted del


Only thing you might want to consider is adding a column for what action was done... i.e. INSERT,DELETE,UPDATE.

Hope this helps.

 

[SQLSister]

First, you will need three triggers, one for insert, one for update, one for delete.

To get the record(s) you want, you use special tables that SQL uses to store the information in temporarily, the inserted and deleted tables, so use inserted as the table naem for the insert and update triggers and deleted as the table name for the deleted trigger and you should have it.

However, from the data you want to send, you have no way of knowing if it was an insert, update or delete which you might want to know later, so I'd also send a field with a code for this to the table too. This is especially true if you are doing an insert and not a delete statement within the delete trigger. (I'm assuming this is an audit table of some sort.)

You might check out Books on line before you start, triggers are tricky and can affect performance, so you really need a good understanding of what they do and how they operate before you start to play with them or you can really mess up your database. A word of caution, don't even attempt to develop a trigger unless you are on a development database. Users of a production database can be so cranky when you take down their ability to ad, change, or delete a record because you had an error in the intial trigger code you wrote.

 

[gojohnnygogogogo]

thank you SQLsister and meangreen.
meaangreen, you mention 'from INSERTED ins' and 'from DELETED del' are these the 'special tables' SQLsister talks about ?

yes is its an audit system, good idea about adding in the word insert ,update or delete so I know what happened.
cheers.

 

[SQLSister]

Yes, he's just choosing to use an alias to make creating the code a little shorter.

 

[MeanGreen]

If you decide to include the action, then you will want to create all three triggers as SQLSister has stated. Then all you have to do is include the appropriate word (make sure it is not one of the reserved words like inserted or deleted).

Hope this helps.

 

[gojohnnygogogogo]

excellent, that works.
thank you.
one more question, where are the INSERTED and DELETED tables ?

 

[MeanGreen]

Inserted and Deleted tables are ONLY used within triggers. They are a form of temporary tables with visibility only in the triggers. You might want to review them in BOL to gain a full understanding or their creation and usage.

Hope this helps.

 

[gojohnnygogogogo]

ok, thanks, I'll read about those tables.
Is it possible to also insert the data before it was amended into the other table aswell ?, so I have a record of the changes made ?

 

[gojohnnygogogogo]

This trigger works fine :

CREATE TRIGGER [TRIGGER NAME] ON [dbo].[Ey]
FOR INSERT, UPDATE
AS
INSERT INTO Mutations (AddDate, Prov, Forename, Surname, Sess, SessConf, TermID)
select GETDATE(), ins.Prov, ins.Forename, ins.Surname, ins.Sess, ins.Sessconf, ins.TermID from INSERTED ins

however, is it also possible to insert the 'old' record into the mutations table aswell ?

 

[MeanGreen]

Use the Deleted table to insert the 'old' record.

CREATE TRIGGER [TRIGGER NAME] ON [dbo].[Ey]
FOR UPDATE
AS
INSERT INTO Mutations (AddDate, Prov, Forename, Surname, Sess, SessConf, TermID,ACTION)
select GETDATE(), ins.Prov, ins.Forename, ins.Surname, ins.Sess, ins.Sessconf, ins.TermID,'AFTER' from INSERTED ins

INSERT INTO Mutations (AddDate, Prov, Forename, Surname, Sess, SessConf, TermID,ACTION)
select GETDATE(), del.Prov, del.Forename, del.Surname, del.Sess, del.Sessconf, del.TermID,'BEFORE' from DELETED del

Hope this helps.

 

[gojohnnygogogogo]

thank you.

 

[rt63]

Just to add a bit. You really do not need 2 triggers to handle this. You can define a single trigger for insert,update and delete and write the update trigger code in it. It will work fine for all three cases.

1. Insert : Only inserted table will have records
2. Delete : Only deleted table will have records
3. Update : Both will have records.

So update trigger code will work fine for all.
RT