General discussion


Extend functionality with Instead Of triggers

By MaryWeilage Editor ·
This week's SQL Server e-newsletter discusses how you can extend functionality by using Instead Of triggers.

Is this tip useful to you? Have you used Instead Of triggers to extend functionality? If so, please share your experiences with your peers.

If you aren't subscribed to the free SQL Server e-newsletter, click the following link to automatically sign up:

This conversation is currently closed to new comments.

Thread display: Collapse - | Expand +

All Comments

Collapse -

Use Of Instead Of Triggers

by spaliwal In reply to Extend functionality with ...

Instead Of Triggers are really useful for developers. One useful purpose of such triggers is that they can be used for audit purpose within the database .If you want to record every change that affects the state of data within your database then you can use instead off triggers to execute every DML statement that is issued and at the same time you can record the action within the audit table .

Collapse -

Don't need Instead Of triggers for these examples

by Dan Cooperstock In reply to Use Of Instead Of Trigger ...

For both the example in the article, and the auditing purpose in the previous message, you can use normal triggers. In both cases, you want the normal DELETE (or INSERT or UPDATE) to happen, and in addition you want to log something else in another table. You can do that with a normal trigger!

It would be nice to see an example (other than the updateable view, which I guess is a good one) where you really do need to use an Instead Of trigger rather than a normal one.

Collapse -

Hacker Tool?

I think a good example of where to use an "Instead of" trigger would be in working with rogue or untrained developers.

When the db server is used in a production environment, DBA's often make a rule forbidding dynamic SQL - which rule is often ignored.

Using an "Instead Of" trigger could be used to replace their ill-advised code with a statement that (for example) deletes their employee record and revokes their login privledges.

Another better use might be to use the SP to check the parameters in the SQL to ensure no injected SQL was involved, or to reroute the query to a remote server - one you don't necessarily want the world to know about.

Of course, a hacker (with just one statement)could redirect all DML to the FBI or al Quaeda.

Collapse -

Our Use of an Instead Of trigger

by SWilliams In reply to Don't need Instead Of tri ...

At my company, we are developing a new piece of software that uses email as a login for our clients; therefore duplicate emails in our database could cause big problems for our clients. We have other software that has functionality to allow our users (employees, not clients) to update our clients' email addresses. Rather than rewrite that software (which we didn't have time to do), we used an instead-of trigger to make sure none of our users try to add an email address that already exists in our database. If someone tries to insert a duplicate email, the trigger writes all the information to a tracking table, then inserts 'DupeEmail@...' instead of the email the user was entering that would have caused a duplicate. Then another process checks for these duplicates and sends emails to the appropriate people to allow them to correct them. So in our case, the instead-of trigger actually changes the inserted value.

Related Discussions

Related Forums