Database trigger

The database trigger means operation carried out in response to the event for the table automatically. The trigger is used to automate the management of the data state by the data operation language. I can perform limitation of the data operation, a record of the operation, inspection of the change operation by using trigger.

Table of contents

Kind of the trigger

I greatly divide it into trigger, and there are two kinds of "line trigger" and "the sentence trigger".

Line trigger

It is carried out for line each affected in the table. I can read before change or the line after the change using a pseudoline variable called OLD, NEW.

Sentence trigger

Only once is carried out for INSERT, UPDATE, DELETE sentence.

In addition, the trigger has the following attributes.

"BEFORE" or "AFTER"

I appoint the timing when trigger is carried out.

"INSTEAD OF"

I carry out trigger in substitution for an original sentence.

"WHEN"

I appoint a condition type to start trigger.

Generally, the trigger is started by three following events. I cannot have an influence on the data acquisition by the SELECT sentence in the trigger.

• INSERT (the insertion of the new line)
• UPDATE (change of the existing line) / UPDATE OF (change of the specific line of the existing line)
• DELETE (deletion of the existing line)

Trigger of Oracle

Trigger for change of the schema called "schema level trigger" and login / logoff of the user was added other than standard trigger in Oracle 9i in Oracle Database.

• INSERT
• UPDATE
• DELETE
• AFTER CREATE
• BEFORE/AFTER ALTER
• BEFORE/AFTER DROP
• BEFORE LOGOFF / AFTER LOGON

Trigger of Microsoft SQL Server

I can define the INSTEAD OF trigger for a view other than standard trigger in Microsoft SQL Server. In addition, data definition language (DDL) trigger was added in Microsoft SQL Server 2005.

• INSERT
• UPDATE
• DELETE
• DROP TABLE
• CREATE TABLE
• ALTER TABLE
• Login

Trigger of PostgreSQL

In PostgreSQL, standard trigger is supported by version 6.2 (1997). Furthermore, I supported UPDATE OF, WHEN phrase in TRUNCATE event, 9.0 in version 8.4.

• INSERT
• UPDATE / UPDATE OF
• DELETE
• TRUNCATE

I do not describe the processing carried out in trigger directly and give it for a function.

Grammar:

 CREATE TRIGGER name {BEFORE | AFTER} {event [OR ... ]}     ON table [FOR [EACH] {ROW | STATEMENT}]     [WHEN (...)] EXECUTE PROCEDURE funcname (arguments) 

Trigger of MySQL

In MySQL, standard trigger was supported in version 5.0.2.

• INSERT
• UPDATE
• DELETE

Grammar:

 CREATE TRIGGER salary_trigger     BEFORE UPDATE ON employee_table     REFERENCING NEW ROW AS n, OLD ROW AS o     FOR EACH ROW     IF n.salary <> o.salary THEN        ...     END IF; 

Outside link

• Oracle CREATE TRIGGER
• DB2 CREATE TRIGGER statement
• Microsoft SQL Server CREATE TRIGGER / DROP TRIGGER
• PostgreSQL CREATE TRIGGER
• MySQL Database triggers

It is acquired by "https://ja.wikipedia.org/w/index.php?title= database trigger &oldid=46771788"

This article is taken from the Japanese Wikipedia Database trigger

This article is distributed by cc-by-sa or GFDL license in accordance with the provisions of Wikipedia.

Wikipedia and Tranpedia does not guarantee the accuracy of this document. See our disclaimer for more information.

In addition, Tranpedia is simply not responsible for any show is only by translating the writings of foreign licenses that are compatible with CC-BY-SA license information.