Where do you need to use triggers?

Savita Joshi

Hi,

Where do we need to use triggers,how do we decide on that in case in asp.net application. Please give examples in order to understand it better.

6 Replies

Know the answer? Post it — somebody with the same question will find it here.

Andrew Fenster15y ago
Accepted answer
You don't NEED to use triggers anywhere, because there are always alternatives.

A database trigger is sometimes handy. The exact method varies from database to database. The basic idea is that when a particular table gets updated, the trigger fires and executes a SQL call, stored procedure or function.

Triggers can be very useful, but there are two potential problems (at least):

(1) If you use a lot of them, your business logic will get very complicated and hard to debug.

(2) Triggers can be very inefficient. The trigger code runs EVERY time the database table gets updated. So suppose you added a trigger so that someone gets notified each time an order over $1,000,000 gets entered. Suppose you only have an order that large every several months. So you now have a trigger that fires each time an order gets entered. Each time the trigger will evaluate the order size and determine whether to send a notice. That's a lot of extra work for a situation that only arises every few months. So that would not be a good situation to use a trigger.
Andrew Fenster15y ago
When to use triggers or not is a matter of style. You can always do the same thing is a data access layer.

Personally, I don't like to use them for business logic. Then you have part of your business logic in the code and part in the database, and you can't easily debug. It's more error prone.

Of the four reasons Ankit listed, the first and the third seem like sensible places for triggers. Use them sparingly.
Shirsendu Nandi15y ago
Trigger is a special kind of store procedure occurs when some sql statement is being executed.trigger is usefull when you are using some tranjactional operation or large amount of users want to access some data.In that situation trigger have been used for locking and concurrent tranjaction.
Ankit Nandekar15y ago
Because a trigger resides in the database and anyone who has the required privilege can use it, a trigger lets you write a set of SQL statements that multiple applications can use. It lets you avoid redundant code when multiple programs need to perform the same database operation.

You can use triggers to perform the following actions, as well as others that are not found in this list:
- Create an audit trail of activity in the database. For example, you can track updates to the orders table by updating corroborating information to an audit table.
- Implement a business rule. For example, you can determine when an order exceeds a customer's credit limit and display a message to that effect.
- Derive additional data that is not available within a table or within the database. For example, when an update occurs to the quantity column of the items table, you can calculate the corresponding adjustment to the total_price column.
- Enforce referential integrity. When you delete a customer, for example, you can use a trigger to delete corresponding rows (that is, rows that have the same customer number) in the orders table.
For more information visit this below link
http://publib.boulder.ibm.com/infocenter/idshelp/v10/index.jsp?topic=/com.ibm.sqlt.doc/sqltmst316.htm>
Suthish Nair15y ago
I think Andrew fairly explained you about trigger and the thread subject. Rest are keywords of triggers, how to write triggers. So what is your exact query?
Savita Joshi15y ago
@ Andrew,

Please can you tell me where is it absolutely necessary to use triggers.. How do you decide whether it should be INSTEAD OF TRIGGER OR AFTER TRIGGER..