September 12, 2003 at 12:31 pm
I've worked with INSERT, UPDATE and DELETE triggers a little, but am not sure if the following can be done:
In an AFTER INSERT trigger,
I need to test if a record exists
If it does, UPDATE it
If it doesn't exist, INSERT it
I'm thinking first I need to do a comparison between the two tables, but am not sure if that would be a SELECT involving a record count test or what ?
All advice would be much appreciated.
Beth L
September 12, 2003 at 4:35 pm
Try something like the following...
IF EXISTS(SELECT * FROM...)
BEGIN
put your update here
END
ELSE
BEGIN
put your insert here
END
Gary Johnson
Microsoft Natural Language Group
DBA, Sr. DB Engineer
Gary Johnson
Microsoft Natural Language Group
DBA, Sr. DB Engineer
This posting is provided "AS IS" with no warranties, and confers no rights. The opinions expressed in this post are my own and may not reflect that of my employer.
September 13, 2003 at 4:03 am
Can multi records be involved? If multi then some records may exist and some may not exist in the second table
September 13, 2003 at 10:42 am
You need to write the trigger as if multiple records exist, so do the check as a join between the "inserted" table and the other table and perform the update the same wya. DO NOT USE variables to capture data since they will not handle multiple records.
Steve Jones
http://www.sqlservercentral.com/columnists/sjones
The Best of SQL Server Central.com 2002 - http://www.sqlservercentral.com/bestof/
September 15, 2003 at 7:45 am
Thanx for the responses.
I will need to check for multiple records, so I figure I'll have to use a loop somewhere. I'm used to VB/VBA syntax, but should be able to use the Books On-Line to figure that part out. If not, I'll be back 🙂
Beth
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply