By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
437,766 Members | 1,252 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 437,766 IT Pros & Developers. It's quick & easy.

Help with update trigger

P: n/a
Hi all,
I know squat about triggers so was hoping somebody could point me in the
right direction. I wanted to copy an email address field from a salesman
table to a note field in a customer table. Seems easy enough for a one time
update. But I would like to add a trigger to auto-update the customer table
anytime an email address changes in the saleman table or a new salesman
record is added.

Here's my update script (this copies the salesman email address to each of
his customers)
UPDATE CUSTOMERS
SET NOTE_5 = SALESMAN.EMAIL_ADDR
FROM CUSTOMERS INNER JOIN
SALESMAN ON CUSTOMERS.SLSPSN_NO = SALESMAN.SLSPSN_NO
How can I turn this into a trigger for automatic updates?

Thanks for any help.
May 5 '06 #1
Share this Question
Share on Google+
1 Reply


P: n/a
rdraider (rd******@sbcglobal.net) writes:
I know squat about triggers so was hoping somebody could point me in the
right direction. I wanted to copy an email address field from a
salesman table to a note field in a customer table. Seems easy enough
for a one time update. But I would like to add a trigger to auto-update
the customer table anytime an email address changes in the saleman table
or a new salesman record is added.

Here's my update script (this copies the salesman email address to each of
his customers)
UPDATE CUSTOMERS
SET NOTE_5 = SALESMAN.EMAIL_ADDR
FROM CUSTOMERS INNER JOIN
SALESMAN ON CUSTOMERS.SLSPSN_NO = SALESMAN.SLSPSN_NO
How can I turn this into a trigger for automatic updates?


CREATE TRIGGER salesman_tri FOR INSERT, UPDATE ON SALESMAN AS
UPDATE CUSTOMERS
SET NOTE_5 = i.EMAIL_ADDR
FROM CUSTOMERS c
JOIN inserted c.SLSPSN_NO = i.SLSPSN_NO

"inserted" is a virtual table that holds the row that were inserted, or
the after-image of the updated rows.

"deleted" is a sister table that holds deleted rows, or the before-image
of the updated rows.

Note that triggers fires once per statement, so these tables can include
many rows.

You can only access these tables directly in a trigger.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
May 5 '06 #2

This discussion thread is closed

Replies have been disabled for this discussion.