Creation and use of SQL Server and Oracle triggers

SQL Server

1 Creating a Trigger

Gobeginif (object_id (' wmy ', ' tr ') is not null) DROP trigger wmyend; Gocreate TRIGGER Wmyon studentinstead of Insertasbegin    INSERT into Student (number,name) VALUES (1205, ' role 2 '); End;--begin and end are equivalent to {}, is a block of statements, which is not available here for the demo--on followed by the indication that the expression acting on the table--on student below (Instead of INSERT) can be changed to (Instead Of,after, For) (Update,delete,insert)

Parsing (the Insert trigger listed above is an example of other similarities) (the following sequence is an example of the sort of data inserted in the table)

• When instead of, the SQL statement in the trigger replaces the INSERT statement you executed in the text, that is, when you execute an INSERT statement anywhere, the statement actually does not execute and the statement inside the trigger executes
• When for a, the SQL statement in the trigger executes with the INSERT statement that you executed in the text, only the execution of the trigger that precedes the text
• When an SQL statement in a trigger is executed with an INSERT statement that executes in your text as after, the execution is only later in the trigger (as is said on the web, as is the case with the for effect)

Excerpt from the online:

1 "Instead of" trigger

• The "Instead of" trigger is executed before the actual "insert" is performed. In addition to tables, the "Instead of" trigger can also be used for views to extend the update operations that the view can support.
• The "Instead of" trigger overrides the SQL statement you want to execute, meaning that the SQL you want to execute does not "actually execute"

2 "after" trigger

• The "after" trigger is triggered after an insert, update, or deleted statement executes. The "after" trigger can only be used for tables.
• The "after" trigger is used primarily for tables after modification (after an INSERT, update, or delete operation) to modify other tables

SQL Server creates two dedicated tables for each trigger: the inserted table and the deleted table .

• These two tables are maintained by the system, which exist in memory rather than in the database and can be understood as a virtual table.
• The structure of the two tables is always the same as the structure of the table that the trigger acts on.
• The two tables associated with the trigger are also deleted after the trigger execution is complete.
• The deleted table holds all rows that are to be removed from the table because of execution of the DELETE or UPDATE statement.
• The inserted table holds all rows to be inserted into the table because of an INSERT or UPDATE statement execution.

Goinsert into Student (number,name) VALUES (1807, ' characters ');

Deep parsing using sequences and inserted tables

Use [osmp]beginif EXISTS (SELECT * from sysobjects WHERE name = "Person") and not EXISTS (SELECT * from person) DROP table Personendgocreate TABLE person (num,int s_score,int S_name,nvarchar (+), primary key (num)) beginif EXISTS (SEL ECT * from sysobjects WHERE name = ' Student ') and not EXISTS (SELECT * from Student) DROP table Studentendgocreate table St Udent (Score,int Name,nvarchar (+), primary key)--Create sequence beginif EXISTS (SELECT * from sysobjects WHERE name = ' student_seq ') DROP SEQUENCE student_seqendcreate SEQUENCE student_seqminvalue 1MAXVALUE 999999999999999999START with 1INCREMENT by 1CACHE 20; Gobeginif (object_id (' wmy ', ' tr ') is not null) DROP trigger wmyend; Gocreate TRIGGER Wmyon Studentinstead of insertasbegin Insert into person (num,s_score,s_name) Select Next value for Stu Dent_seq,score,name from Inserted
--This is not meant to be used primarily for understanding, the following student insert statement is performed, and the database maintains a table inserted that is the same as the student data structure.
--here the table and sequence values are used to insert the person, but actually do not perform the student insert, if you want to student also perform the change instead to for or afterend; Goinsert into Student (score,name) VALUES (' 1 ', ' m ');

Episode gets the current value of the sequence

Goselect current_value from sys.sequences WHERE name = ' Student_seq '

Oracle triggers are similar to SQL Server, where only code is displayed

begin EXECUTE IMMEDIATE ' DROP TABLE person '; EXCEPTION when OTHERS and then NULL; END;    CREATE TABLE person (num integer, S_score integer, S_name NVARCHAR2 (+), primary key (num)) BEGIN    EXECUTE IMMEDIATE ' DROP TABLE Student '; EXCEPTION when OTHERS and then NULL; END; Create TABLE Student (score INTEGER, name NVARCHAR2, primary key)--creating sequence begin EXECUTE Immediat    E ' DROP SEQUENCE student_seq '; EXCEPTION when OTHERS and then NULL; END; CREATE SEQUENCE student_seqminvalue 1MAXVALUE 999999999999999999999999999START with 1INCREMENT by 1CACHE; 

CREATE OR REPLACE TRIGGER tr_inst_devicebefore INSERT on m_deviceentityfor each rowbegin    select Student_seq. Nextval into:new.num from dual;    --select ' A ' | | Trim (To_char (: New.num, ' 00000000 ')) Into:new.score from dual;    --This two-statement function does not practice, the first to take out the next value of the sequence is inserted into the new table (the new table is similar to inserted in SQL Server)-    -Change the score to a_0000001 as a style, this statement is only a reference here useless also can not run (Because of the field type of the table) END;

GO
Insert into Student (score,name) VALUES (' 1 ', ' m ')

How to represent the combination of values and tables in a record set

Insert into person (n, num, name) Select Next value for Entity_seq,number,name from Student where Number=114;--next value f The or entity_seq sequence is added with the Select field combination INSERT into person (n, num, name) select CAST (+ as int), number,name from Student where number =114;--cast (as int) values and field combinations added

　　

Creation and use of SQL Server and Oracle triggers