Re: Triggers - Inserted and Deleted Tables
From: Narayana Vyas Kondreddi (answer_me_at_hotmail.com)
Date: 07/15/04
- Next message: John R.: "Stored Procedure Parameters"
- Previous message: David Portas: "Re: Database Structure Debate"
- In reply to: Richard J: "Q: Triggers - Inserted and Deleted Tables"
- Messages sorted by: [ date ] [ thread ]
Date: Thu, 15 Jul 2004 15:55:39 +0100
If you have a DML statement that affects more than one row, and if you are
using a SELECT statement to assign a variable from the 'deleted' table, your
trigger will fail - I mean, you won't get an error, but the trigger action
will be logically incorrect.
The 'deleted' table will indeed contain two rows in your case, but your
SELECT query is going to assign the variable to only one of those rows'
values.
If you want to process all rows, you should join the 'deleted' table with
whatever table, using the key values. Or if needed, you'll have to loop
through all the rows of 'deleted' table.
-- HTH, Vyas, MVP (SQL Server) http://vyaskn.tripod.com/ Is .NET important for a database professional? http://vyaskn.tripod.com/poll.htm "Richard J" <RichardJ@discussions.microsoft.com> wrote in message news:A54003E1-9896-4C36-8803-5C9BC7ABFDC2@microsoft.com... Hi group, I read the BOL concerning the inserted and deleted tables, but the text seemed to be missing one point that I am attempting to reconcile. Say I have the following in my trigger: SELECT @OldBalance = Balance FROM deleted I then turn around and do the following: SELECT @Account = AccountNumber, @Share = ShareNumber, @NewBalance = Balance FROM inserted I then turn around and process an overdraft table if the new balance is >= 0 and the old balance is < 0. How does SQL Server manage the inserted and deleted tables if more than one row is affected? For example, the above example is a financial transaction and I update an account holders checking and savings account. The SELECT from the deleted table will pull back two rows. How does SQL Server know which record in deleted goes with which record in inserted? I ask this because the trigger successfully works, but I don't understand how. Any insight given will be most appreciated. Thanks. Richard
- Next message: John R.: "Stored Procedure Parameters"
- Previous message: David Portas: "Re: Database Structure Debate"
- In reply to: Richard J: "Q: Triggers - Inserted and Deleted Tables"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
