Shulou(Shulou.com)06/01 Report--

1. Triggers will take effect as soon as they are created, and sometimes you may need to disable them temporarily. The most common reason is that data loading is involved.

ALTER TRIGGER trigger_name [ENABLE | DISABLE]

2. Two important memory logic tables in Oracle triggers: old and: new. The: old table holds the data before the DML statement, and the: new table holds the data created in the DML statement.

Old new

INSERT-√

DELETE √-

UPDATE √ √

3. Transactions cannot be explicitly committed / rolled back in trigger statements.

Row-level triggers

Row-level triggers execute once for each row affected by the DML statement. The main application maintains data integrity.

Example: two tables are cascaded to update, modify the department number of the department table as well as the department number of the employee table:

CREATE OR REPLACE TRIGGER update_dept

/ * row-level trigger, which is triggered after updating the department table operation * /

AFTER UPDATE ON deptment

FOR EACH ROW

BEGIN

/ * effective use of new and old tables to update the id column value of the old table to the id column value of the new table * /

UPDATE emp SET id=:new.id WHERE id=:old.id

END

UPDATE deptment SET id='yy' WHERE id='01'

SELECT * FROM deptment

SELECT * FROM emp

When inserting, use trigger + sequence to realize the self-increment of integer field:

CREATE OR REPLACE TRIGGER set_no

BEFORE INSERT ON auto

FOR EACH ROW

DECLARE

Sn number (5)

BEGIN

/ * value of trigger preprocessing sequence * /

SELECT myseq.nextval INTO sn FROM dual

: NEW.a: = sn

END

INSERT INTO auto VALUES (21)

SELECT * FROM auto

/ * when inserting, the value of table a column generated by the trigger replaces the user's input * /

Statement-level trigger

It is only related to statements, not rows, and does not involve data integrity issues.

For example: using trigger records to record the actions of users in a table (log processing).

CREATE OR REPLACE TRIGGER dm1_aa

AFTER INSERT OR DELETE OR UPDATE ON aa

BEGIN

IF INSERTING THEN

INSERT INTO mylog VALUES (user,sysdate,'I')

ELSEIF DELETING THEN

INSERT INTO mylog VALUES (user,sysdate,'D')

ELSE

INSERT INTO mylog VALUES (user,sysdate,'U')

END IF

END

Ps. Can you record more than one table? an error occurred with "after insert or delete or update on T1 # T2".

III. Replacement trigger

To solve the limitation of multiple table updates in Oracle view, it can only be used in the view, which belongs to row-level triggers.

For example: insert a new department in a view and insert a new employee to which it belongs:

CREATE OF REPLACE TRIGGER tr_v_e_d

/ * create a replacement trigger on the view * /

INSTEAD OF INSERT ON v_emp_dept

FOR EACH ROW

BEGIN

/ * the replacement trigger inserts the information of the department table before inserting the information of the employee table to which it belongs * /

INSERT INTO deptment VALUES (: new.id,: new.name)

INSERT INTO emp (eid, ename, sex, id) VALUES (: new.eid, new.ename,: new.sex,: new.d)

END

INSERT INTO v_emp_dept VALUES ('456,' test', 'fond,' 33, 'hg')

SELECT * FROM v_emp_dept

Mode-level triggers

Triggers that can be established on the operation of a schema object, such as CREATE, ALTER, DROP, GRANT, REVOKE, and TRUNCATE DDL statements.

[BEFORE | AFTER] trigger_event ON [schema.] SCHEMA

For example: log all objects deleted by the user

CREATE OR REPLACE TRIGGER log_drop_obj

AFTER DROP ON SCHEMA

BEGIN

/ * record operation type, operation object, operation time * /

INSERT INTO dropped_obj VALUES (ORA_DICT_OBJ_NAME, ORA_DICT_TYPE, SYSDATE)

END

CREATE TABLE for_drop (x char)

DROP TABLE for_drop

SELECT * FROM dropped_obj

Database-level triggers

You can create triggers on database events, including startup, shutdown, server errors, login, logout, and so on. These events are instance-scoped and are not associated with a specific table or view.

CREATE OR REPLACE TRIGGER system_startup

AFTER STARTUP ON DATABASE

BEGIN

...

END

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.