Replacing Triggers

Question

Post reply

Replacing Triggers

Jake Shelton

Hall of Fame

Points: 3109
More actions
September 14, 2010 at 5:32 am

#227659

Hi all,
I don't have a particular trigger I'd like to dispense with, I just wanted some sources of best practice on how to replace them, and with what.
Thanks,
Jake.

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

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

Lowell SSC Guru Points: 323518 More actions · Answer 1

well, stating the obvious, it depends on what the trigger does;

triggers that are jsut sending emails because a new record was added can be replaced witha job that scans for changes;

triggers that populate child tables based on the parent table can be replaced by a command accessing the OUTPUT clause to insert into the child table isntead;

triggers that were populating values can sometimes be replaced with defaults;

audit triggers are tracking changes, so you cannot really replace them.

did you have a specific scenario in mind?

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Jake Shelton Hall of Fame Points: 3109 More actions · Answer 2

Lowell (9/14/2010)
well, stating the obvious, it depends on what the trigger does;
triggers that are jsut sending emails because a new record was added can be replaced witha job that scans for changes;
triggers that populate child tables based on the parent table can be replaced by a command accessing the OUTPUT clause to insert into the child table isntead;
triggers that were populating values can sometimes be replaced with defaults;
audit triggers are tracking changes, so you cannot really replace them.
did you have a specific scenario in mind?

No production issue really, I'm going to a new environment soon where they're used widely and I anticipate some kind of audit will need to be done to see how to improve/replace them, so I was just trying to get a step ahead on that. You pretty well resolved my original query with your examples. BUT, you did pique my curiosity and I'd be interested in getting more examples of how each of the first 3 (I've already been looking into audit triggers) were implemented.

Brandie Tarvin SSC Guru Points: 173061 More actions · Answer 3

Lowell (9/14/2010)
well, stating the obvious, it depends on what the trigger does;
...
triggers that populate child tables based on the parent table can be replaced by a command accessing the OUTPUT clause to insert into the child table isntead;
...

On this note, it wouldn't be a bad idea to go through those triggers and verify that none of them are trying to replicate referential integrity. Meaning - duplicating foreign key behavior without using foreign keys (that's what Foreign Keys exist for so why use triggers?).

And logging can be done by many different means, not just triggers.

Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

sanmatrix SSC Veteran Points: 250 More actions · Answer 4

1. If your trigger code is performing DML which can be put into stored procedure then use stored procedure. Ex: if you are inserting a row in record using inline query/stored procedure and the trigger is doing some validation or inserting a record then put the trigger code either after the inline query or inside the stored procedure.

2. If the trigger is performing the task of constraint implementation then better use constraints at database level like foreign key/check/unique and so on.