Trigger is a Program Unit generally a SQL statement or a set of SQL statements which executes automatically when an associated event of an object in a database occurs.
This has basically three components
- Program unit is the automated predefined action item to be executed
- Object on which the action item will be associated
- Event is the condition/situation to be fired of an object
Before going into deep, I would like to mention that, the whole concepts of the trigger implementation are product specific. All the features of the trigger might not be available in a particular product; but it is true that the basic things are available in all the major products. Even when I came to know first time about the trigger, in 1995 then only DML type of triggers were available for manipulating transactional data; now it’s scope has been totally redefined by keeping same concept of trigger having three component mentioned above. Next phase, the concept of DDL triggers came into the picture.
Program Unit
This might be a set of related SQL of an object, any functionality implemented by an executable application.
For example: Different scenarios where possible action unit could be implemented.
For maintaining data integrity by cascading the changes to the related object, it could be maintaining some constrain. In this purpose single SQL or set of SQL could be used as a Program Unit.
For notification purpose by sending messages/alerts using email or other way.
For audit purpose by logging into event log, or some files, or into any table
For populating change history or Summarized data value
The object and event depends on the implementation of trigger. The trigger could be broadly categorized into two – DDL and DML Trigger.
DML trigger mainly used for maintaining transactional data where DDL trigger is used for administrative and auditing purpose. In earlier concept we used to see only DML trigger for INSERT/UPDATE/DELETE operations. The DML trigger could be associated to a Table or a View. Later we got the DDL trigger feature which could be associated to Database, Table, Index etc. Here the operation could be CREATE/ALTER/DROP. Same time there is another factor arises, that is, associated Program Unit will be executed BEFORE or AFTER of the triggering statement execution.
Object
DML trigger – Data Manipulation Language Trigger; generally associated with Table or View when data values are inserted/updated/deleted
DDL trigger: Generally for object creation / alteration or deletion that mean the keywords CREATE, ALTER, and DROP associated with Transact SQL. It’s scope as follows:
- Database level
- Server level
Event
The events depend on the object. If it a table it could be for each row or as whole of a SQL statement.
DML trigger
Table
Statement: This occurs once for executing a SQL statement not depends on the number of rows affected by that statement. It is available in Oracle.
For each row: When a SQL executes, for every row the action will be fired. Again in granular level it could be BEFORE or AFTER
View: Instead of Trigger – Here only we can see only AFTER type operation is available. It has some other limitation too. When any modification or insertion or deletion has been done using instead of trigger, always it checks individuals object’s constrains.
DDL trigger
DDL triggers fire only after the DDL statements whether it’s scope is server level or database level.
DDL statement – CREATE, ALTER, DROP, TRUNCATE, RENAME, ANALYZE, AUDIT, NOAUDIT, COMMENT, ASSOCIATE STATISTICS, DISASSOCIATE STATISTICS, UPDATE STATISTICS, GRANT, REVOKE, DENY statements that can cause the trigger to fire
Apart from above, there are other events related to Database/Server, user logon, server error causes to fire the trigger
Database Startup, Shutdown , Suspend
Logon, Logoff the session
Server Error
The possible usages of the different types of triggers.
DML trigger
It could be used in the following requirements::
- For maintaining data integrity by cascading the changes to the related object, it could be maintaining some constrain. In this purpose single SQL or set of SQL could be used as a Program Unit.
- For maintaining referential integrity constraint, it is always good to use foreign key between parent and child table. [ Cascade deletion, nullifying foreign key’s data for the deletion of parent row. ]
- For he case of data integrity, always good to use default constrains / check constraint, rules by using user defined data type.
For the case of maintaining change history, I will prefer to have the trigger. The OLD/DELETED and NEW/INSERTED value could reference in a easy way using trigger to store change history.
- For the case of populate summarized data, better to use stored procedure and call it explicitly.
- For notification purpose by sending messages/alerts using email or other way.
- Instead of trigger I will suggest to use notification service by developing triggering component. The business logic will be written in these component.
There some points to be keep into mind, where a group of people are responsible/involved to write/maintain the application.
Need to be well documented for each and every triggers; also it’s impacts to the related data, otherwise it will be difficult to know what are the data getting affected as a series of modification. It is always recommended not to use trigger for DML purposed mentioned above.
For the case of nested trigger / recursive trigger, it will very difficult to do the debugging the application. Therefore, I will be recommending to avoid using trigger for data manipulations.
If we would like to keep change history in very limited and important table, trigger is a good choice.
DDL trigger
For audit purpose by logging into event log, or some files, or into any table DDL trigger. When any objects are creating, modifying, dropping all could be logged which could be auditable.
For auditing purpose logon, database startup/shutdown, server error trigger not a bad good choice. Maintaining these type logging are DBA related job. Generally in an organization there are very very limited number of DBAs are working; where good co-ordination is possible and risk factor is very less.
There are few restriction of trigger. For SELECT statement trigger is not applicable. Trigger fired implicitly not explicitly.
