Update Trigger to change fields in four tables

Question

Update Trigger to change fields in four tables

SArcher 38334

Grasshopper

Points: 17
More actions
January 18, 2010 at 2:35 pm

#94414

I am new to writing SQL Triggers and would greatly appreciate any assistance with setting this up in MSSQL 2000.
I have a customer Profile table containing customer records and three related tables that will also need to be updated. When the Status field in the Profile table is updated to "Inactive", I need to change the Customer Type field in the Profile table to "Archive", as well as all records for the same Customer ID in the three related tables.
I have worked out the SQL to update the records, but you have to manually enter each Customer ID and new Customer Type value into the query.
DECLARE @CustID varchar(5), @CustType varchar(15);
SET @CustID = 'XXXXX';
SET @CustType = 'Archive';
UPDATE ProfileTable
SET CustType = @CustType
WHERE CustID = @CustID
;
UPDATE Table1
SET CustType = @CustType
WHERE CustID = @CustID
;
UPDATE Table2
SET CustType = @CustType
WHERE CustID = @CustID
;
UPDATE Table3
SET CustType = @CustType
WHERE CustID = @CustID
I'm hoping to automate this with a trigger, so that when the Customer Status is updated in the Profile table the Customer Type is updated there, as well as in the related tables for the same Customer ID.

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

LutzM SSC Guru Points: 107049 More actions · Answer 1

It sounds like your database structure is far away from being normalized...

Instead of looking for a way to update those 4 tables I strongly recommend to redesign your database to have only one table holding the status of @CustType.

If, for whatever reason, you cannot normalize your database you should call a procedure and let the proc take care of the data change instead of using a trigger.

The trigger you're trying to create won't be able to handle an direct UPDATE statement to your base table covering more than one row. (Something like: "UPDATE YourTable set CustType ='Archive' WHERE CustID <1000" done by the DBA directly from SSMS).

Also (IIRC), a trigger opens a transactions that remains open until processing is done. Therefore, running a trigger across multiple tables might cause blocking.

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Ron McCullough SSC Guru Points: 63877 More actions · Answer 2

If you can not modify the tables then you might consider creating a parameterized stored procedure , true you would have to pass the values to the procedure, but that is really not a big deal. And since you want to be sure that all tables are updated correctly use a start transaction, and if no errors occur then a commit transaction, if an error is encountered then a roll back transaction command to return all tables to their original state, that is the contents will be same as they were before the procedure executed.

in Query Analyzer - click on T-SQL help, for creating a store procedure, and for BEGIN TRANSACTION

Also use help to read on how to use Using @@ERROR - by the way this page has an excellent bit of T-SQL code to show you how to use it effectively.

Takes a little bit on concentration, but once you create your first fully functional stored procedure, you will find it easy to create others.

Guaranteed to be simpler than creating triggers ...

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

Steve Jones - SSC Editor SSC Guru Points: 739010 More actions · Answer 3

In addition, consider using joins to update.

UPDATE ProfileTable

SET CustType = @CustType

from inserted i

WHERE i.CustID = ProfileTable.custID

;