What is it, Trigger?

Question

I was looking about triggers on this site, but found no question about triggers and then:

• What are Triggers?
• When to use a Trigger?
• How to use a Trigger?
• What are the uses and applicability of Triggers?

Answer 1

What are Triggers?

A Trigger, or trigger, is a special type of stored procedure, which is executed whenever there is an attempt to modify the data of a table that is protected by it.

(Source: Wikipedia)

When to use a Trigger?

As triggers are used for cascading updates and exclusions. A common confusion is that they return results in queries, and they impose and maintain low-level referential integrity. Also, you can use triggers to set custom error messages, among other features.

How to use a Trigger?

To create, change or delete a Trigger the instructions are used DML (Data Manipulation Language). On the website of msdn, you find the syntax for the creation of Trigger:

-- SQL Server Syntax  
Trigger on an INSERT, UPDATE, or DELETE statement to a table or view (DML Trigger)  

CREATE TRIGGER [ schema_name . ]trigger_name   
ON { table | view }   
[ WITH <dml_trigger_option> [ ,...n ] ]  
{ FOR | AFTER | INSTEAD OF }   
{ [ INSERT ] [ , ] [ UPDATE ] [ , ] [ DELETE ] }   
[ WITH APPEND ]  
[ NOT FOR REPLICATION ]   
AS { sql_statement  [ ; ] [ ,...n ] | EXTERNAL NAME <method specifier [ ; ] }  

 <dml_trigger_option> ::=  
    [ ENCRYPTION ]  
    [ EXECUTE AS Clause ]  

 <method_specifier> ::=  
    assembly_name.class_name.method_name  

The arguments are (taken from MSDN):

• schema_name: is the name of the scheme to which the DML trigger belongs.
• trigger_name: will identify the Trigger as object of the database.
• table | view: table to which the trigger will be connected, to be fired by actions of insert, update or delete.
• To define the time when the Trigger will be fired:
  • FOR is the default value and causes the trigger to be fired next to the action.
  • AFTER causes the firing to take place only after the action that generated it is completed.
  • INSTEAD OF causes the Trigger is executed in place of the action that generated it.
• INSERT/UPDATE/DELETE: one or more of these options (comma separated) must be indicated to inform the bank which action will trigger the trigger. For example, if the Trigger shall be triggered after all insertion, use shall be made of AFTER INSERT.

According to Rodrigo Almeida, no site code line, some precautions on the implementation of triggers inside the seat shall be taken:

• Use triggers to ensure command execution for a specific table;
• Don’t keep creating triggers to duplicate rules already defined in CONSTRAINTS bank;
• Oracle recommends that we limit our codes to a maximum of 60 lines, in case you have to create something more complex stored procedure, will be more useful;
• ATTENTION: Be careful when creating Triggers firing under an instruction UPDATE in your Table, you cannot change the table because that would fire the Triggers more than N times in the system, and the equipment memory would not withstand causing memory bugs and wrong results.

What are the uses and applicability of Triggers?

Some have already been said above, but there are other different purposes for the triggers, among them we can highlight:

Generation of values derived from database columns automatically, audit, synchronized replication of tables, prevention of invalid transactions, application of referential integrity, imposition of security authorization, event log and access storage to database tables.

(Source: devmedia)

To understand the utility, imagine a database with many records. Each employee of the company earns by the hour and you need to update the hours bank of each employee every day. When we created the Triggers, we can define what should be changed in the table in a single file and then trigger a single DML command, update, for it, so it will update all the records at once.

References:

• Introduction to triggers
• Triggers
• Triggers - Wikipedia
• Triggers PL/SQL: know when and why to use
• Triggers in SQL Server: theory and practice applied in a real situation
• Using triggers
• CREATE TRIGGER (Transact-SQL)

Answer 2

Trigger, as his name says, is a Gatilho.

You use a Trigger to trigger a function in the database at a given database event, example: INSERT, UPDATE and DELETE.

A common use of Trigger is user log, for example, every time a record is inserted in table X, Trigger will trigger an event for a log function that will take the data from this INSERT and create a user log. Log version, when a user updates a record to Trigger triggers an event to a log function that will take the current record data and new data from UPDATE and create a log telling what has changed.

It is common to see legacy applications with business rule in Procedure using triggers to execute business rule when there is a INSERT/UPDATE/DELETE.

In my opinion the uses and applicability of triggers depends on how you want to develop the application, if you do not want to leave certain responsibilities with the application and want to pass them to the database.

I’m not going to put code here because it’s been a long time that I don’t touch it, but take a look at this post http://www.devmedia.com.br/introducao-a-triggers/1695 , he talks more about Rigger and who knows how to clear all your doubts.