Sql Triggers Examples

EXAMPLES ON LIBRARY DATABASE

A trigger is a specialized stored procedure that can execute either on a data modification, known as a Data Modification Language (DML) trigger, or can execute on a data model action, such as CREATE TABLE, known as a Data Definition Language (DDL) trigger. 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 DML Trigger

Triggers have many uses. Perhaps the most common for a DML trigger is to enforce a business rule; for example, when a customer places an order, check that they have sufficient funds or that you have enough stock; if any of these checks fail, you can complete further actions or return error messages and roll back the update.
DML triggers can be used as a form of extra validation, for example, to perform quite complex checks on data that a constraint could not achieve. Keep in mind that using constraints instead of triggers gives you better performance, but 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. For example, when you add an order, you would create a DML trigger that would reduce the number of that item
in stock. Finally, DML triggers can be used to create an automated audit trail that generates a change history for each record.
We can create separate triggers for any table action except SELECT, or triggers that will fire on any combination of table actions. Obviously, as no table modifications occur on a SELECT statement, it is impossible to create such a trigger. There are three main types of triggers:
• INSERT trigger
• DELETE trigger
• UPDATE trigger
You can also have a combination of the three types of triggers. Triggers can update tables within other databases if desired, and it is also possible for triggers to span servers as well, so don’t think the scope of triggers is limited to the current database.
It is possible for a trigger to fire a data modification, which in turn will execute another trigger, which is known as a nested trigger. For example, imagine you have Table A, which has a trigger on it to fire a modification within Table B, which in turn has a trigger on it that fires a modification within Table C. If a modification is made to Table A, then Table A’s trigger will fire, modifying the data in Table B, which will fire the trigger in Table B, thus modifying data in Table C. This nesting of triggers can go up to 32 triggers deep before you reach the limit set
within SQL Server; however, if you start getting close to that sort of level, you either have a very complex system, or perhaps you have been overly zealous with your creation of triggers!
It is possible to switch off any nesting of triggers so that when one trigger fires, no other trigger can fire; however, this is not usually the norm. Be aware that your performance will suffer greatly when you start using nested triggers; use them only when necessary.

CREATE TRIGGER Syntax

When building a trigger, it can be created for a single action or for multiple actions. To expand on this, a trigger can be for insertion of a record only, or it could cover inserting and updating the record.

• CREATE TRIGGER trigger_name: First of all, as ever, you need to inform SQL Server what you are attempting to do, and in this instance, you wish to create a trigger. The name for the trigger must also follow the SQL Server standards for naming objects within a database.
In this chapter, you name the triggers starting with tg to indicate the object is a trigger, followed by the type of trigger (ins for insert, del for delete, and upd for update), and then the name of the root table the trigger will be associated with.
• ON {table|view}: It is then necessary to give the name of the single table or view that the trigger relates to, which is named after the ON keyword. Each trigger is attached to one table only.
• [WITH ENCRYPTION]: As with views and stored procedures, you can encrypt the trigger using the WITH ENCRYPTION options so that the code cannot be viewed by prying eyes.
• {FOR|AFTER|INSTEAD OF}:
• The FOR|AFTER trigger will run the code within the trigger after the underlying data is modified. Therefore, if you have any constraints on the table for cascading changes, then the table and these cascades will complete before the trigger fires. You either specify FOR or AFTER.
• INSTEAD OF: The most complex of the three options to understand as a trigger defined with this option will run the T-SQL within the trigger rather than allowing the data modification to run. This includes any cascading. To clarify, if you have an INSTEAD OF trigger that will execute on a data INSERT, then the insertion will not take place.
• {[INSERT] [,] [UPDATE] [,] [DELETE]}: This section of the syntax determines on what action(s) the trigger will execute. This can be an INSERT, an UPDATE, or a DELETE T-SQL command. As mentioned earlier, the trigger can fire on one, two, or three of these commands, depending on what you wish the trigger to do. Therefore, at this point, you need to mention which combination of commands, separated by a comma, you wish to work with.
• AS: The keyword AS defines that the trigger code has commenced, just as the AS keyword defined the start of a stored procedure. After all, a trigger is just a specialized stored procedure.
• [{IF UPDATE (column) [{AND|OR} UPDATE (column)]]: This option can be used within a trigger that is not available within a stored procedure, and that is the test to check whether a specific column has been modified or not. This happens through the use of the UPDATE() keyword. By placing the name of the column to test in between the parentheses, a logical TRUE or FALSE will be returned depending on whether the column has been updated or not. The deletion of a record will not set the UPDATE test to TRUE or FALSE, as you are removing an item and not updating it. An INSERT or an UPDATE record manipulation will set the UPDATE test to the necessary value.
• COLUMNS_UPDATE(): This has functionality similar to UPDATE(), but instead of testing a specific named column, it tests multiple columns in one test.
• sql_statements: At this point you code the trigger just like any other stored procedure.

TO SEE EXAMPLES ON LIBRARY DATABASE CLICK