New to SQL Triggers

[rafeman]

Sorry for my ignorance buy i'm relatively new to triggers. I want to create what I believe is a simple trigger.

I have a table called ACCOUNTS with the following fields ACCNT, BSORT, BACCOUNT, BNAME, USER.

I have another table called BANKCHGS.

What i'm trying to achieve is when the application changes any of the fields in ACCOUNTS before it adds the new details it triggers an inserts of a new row with the old details such as;

ID (sequential no.), ACCNT, BSORT, BACCOUNT, BNAME, USER & date when changed.

Any assistance appreciated.

 

[bborissov]

What happens when the user DELETE the record?
What happens when the user INSERT new the record?
If it is ONLY for UPDATE that is easy:

CREATE TRIGGER SomeTriggerName OF TableName
       FOR UPDATE
AS
 BEGIN
    INSERT INTO BANKCHGS (ACCNT, BSORT, BACCOUNT, BNAME, USER, DATECHANGED)
    SELECT ACCNT, BSORT, BACCOUNT, BNAME, USER, GETDATE()
          FROM DELETED
    IF @@ERROR > 0
       ROLLBACK
 END

NOT TESTED. Test it first

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.
Microsoft MVP VFP

 

[SQLDenis]

that will log even if you do

UPDATE ACCOUNTS
SET BNAME =BNAME

so basically nothing has changed



rafeman , read and run the code here to understand more
Fun With SQL Server Update Triggers

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[SQLSister]

Since you are new to triggers, I'll give you some general trigger advice (As Borislav has already given you the ciode that should get you started).

First, triggers always process on the entire batch that was updated, insderted or deleted not on a row by row basis. So when you write your code, you need to always assume a multi-row insert, update or delete is possible and the trigger must account for that. Borislav's trigger code does just that. No matter how many records are updated, they will all be placed into the other table.

A cursor is one way that people account for multiple row updates or inserts or deletes, but do not fall into the trap of ever using a cursor in a trigger unless you want your application to be slow and possibly to block all other users when a large update is done. Cursors are bad most of the time, in a trigger they can be disastrous.

Triggers use two pseudotables, inserted and deleted, that are only avaliable in triggers. As you can see from Borislav's code above, he uses the deleted table which contains the old data. Inserted would contain the new values.

You will find that inserted and deleted do not store text and ntext datatypes. If you need to insert these values as well as varch, nvarchar, int, datetime, etc, then you will need an extra join to the original table to grab these values.

One key to successful trigger development is to test, test, test. Make sure you test multiple record updates as well as single record updates.


"NOTHING is more important in a database than integrity." ESquared

 

[rafeman]

thanks very much for your help and advice all. I will have a play with this and see what I come up with! Thanks again