A trigger is a specialized stored procedure that can execute either on a data modification, or can execute on a data model action. DML triggers are pieces of code attached to a specific table that are set to automatically run in response to an INSERT, DELETE, or UPDATE command. However, a DDL trigger is attached either to an action that occurs within a database or within a server.
The common use of a DML trigger is to enforce a business rule. Triggers are the better choice when dealing with complex data validation. Another use for a DML trigger is to make changes in another table based on what is about to happen within the original triggered table. Finally, DML triggers can be used to create an automated audit trail that generates a change history for each record.
Triggers can update tables within other databases if desired, and it is also possible for triggers to span servers as well.
The ‘instead of’ trigger cannot be created on a view which has been created with check option.
Limitations
1 The ‘create trigger’ statement should be the first statement in the batch.
2 Triggers can only be created in the current database, although they can reference outside objects
3 Triggers cannot be created on a temp or system table. Temp tables can be referenced by triggers. Instead of referencing system tables, you can use information schema views.
4 If a table contains a foreign key with an update or delete cascade, then ‘instead of’ triggers should not be defined on that table.
5 You cannot alter, create, load, or drop database from a trigger. And also, you cannot use reconfigure, restore database, or restore log commands.
Instead Of the primary advantage of the ‘instead of’ trigger is that they allow views that would not be updatable; to support updates. A view that has multiple base tables must use an instead of trigger to support inserts, updates, and deletes.
“Instead of” triggers override the triggering statement. Instead of triggers are most useful for extending the types of updates that a view can support. Instead of triggers provide the logic to modify multiple base tables through a view or to modify base table that contains the following columns.
Timestamp data type, compute columns, and Identity columns.
DDL Triggers you can use DDL triggers to perform administrative tasks, such as auditing and regular database operations. The DDL triggers cannot be instead of triggers.
DDL triggers can fire in response to a T-SQL event processed in the current database or, on the current server. The scope of a DDL trigger depends on the event.
The database-scoped triggers are stored as objects in the database in which they are created. You can get information about them from sys.triggers catalog view. The server-scoped triggers are stored as objects in the master database. You can obtain information about them from sys.server_triggers catalog view in any database.
Nested Triggers - triggers can be nested up to 32 levels. The ‘nested trigger’ option of the server will be used whether to or not this behavior is allowed. By default, the value of this option will be set to 1 which allows nesting, and 0 does not allow cascading of triggers.
You should use separate triggers to cascade data modification across multiple tables. Because triggers execute within a transaction, a failure at any level of a set of a nested triggers cancels the entire transaction.
Note- only direct recursion is prevented when the recursive_triggers option is set to off. To disable indirect recursion, set the ‘nested triggers’ to 0.
Ex: alter database shiva
set recursive_triggers off
sp_configure 'nested triggers',0
reconfigure
When not to use triggers the more trigger you define, the more actual work has to be done by the server. Triggers can hold open locks and create additional transactional log overhead for the transaction. Triggers should be always avoided when a stored procedure or application program can perform the same work.
Triggers should never be used simple data validations. The standard features in sql server will consume fewer resources and produce less impact on the performance.
Federated data for databases that are required to support a highly available system, you can establish a federation of database servers. In this way, you can spread the processing load across a group of database servers. For this you need to horizontally partition the data into a set of tables of across multiple servers. Each table is placed on a different resource.
Once the data has been partitioned and the views created, the problem that must be addressed is adding, updating, and deleting of data in these tables. To resolve you need to create ‘instead of’ triggers on these views that you created to hide the member tables from the application. These triggers will contain the business logic needed to determine which member table should be affected.
Auditing if you need to track the changes made to the data to audit them later, you need to create trigger that writes audit records for each modified record to a separate table. You have access to information such as the user name, host machine, application name, date and time of the change, that can be included to your audit log along with the actual before and after pictures of the data.
Monitoring and Notifications another advantage of using triggers is monitoring certain types of changes and sending an e-mail notification to the appropriate user. Triggers can also be used to send notifications to a DBA when certain errors occur while processing. It helps create a proactive support system rather than a reactive one.
Cross database referential integrity this is possible with triggers. You will need to create triggers to enforce these conditions on both sides of each link. You cannot check these things with standard constraints, such as foreign key.
No comments:
Post a Comment