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

What is the syntax of ORACLE trigger and case analysis? in view of this problem, this article introduces the corresponding analysis and solution in detail, hoping to help more partners who want to solve this problem to find a more simple and feasible method.

SELECT * FROM dept_sal

Example 1 Mel-create a trigger that records the deleted data of the table

-- create tables

CREATE TABLE employee

(

Id VARCHAR2 (4) NOT NULL

Name VARCHAR2 (15) NOT NULL

Age NUMBER (2) NOT NULL

Sex CHAR NOT NULL

);

DESC employee

-- insert data

INSERT INTO employee VALUES ('e101 recording, writing, etc.)

INSERT INTO employee VALUES ('e102 recording, writing, etc.)

-- create a record table

CREATE TABLE old_employee AS

SELECT * FROM employee

DESC old_employee

-- create a trigger

CREATE OR REPLACE TRIGGER tig_old_emp

AFTER DELETE ON employee--

FOR EACH ROW-statement-level trigger, that is, one trigger per line

BEGIN

INSERT INTO old_employee

VALUES (: old.id,:old.name,:old.age,:old.sex); -: old represents the old value

END

/

-- Let's test it.

DELETE employee

SELECT * FROM old_employee

Example 2 Mel-create a trigger to insert data using the view

-- create tables

CREATE TABLE tab1 (tid NUMBER (4) PRIMARY KEY,tname VARCHAR2 (20), tage NUMBER (2))

CREATE TABLE tab2 (tid NUMBER (4), ttel VARCHAR2 (15), tadr VARCHAR2 (30))

-- insert data

INSERT INTO tab1 VALUES (101 Zhaoqi 22)

INSERT INTO tab1 VALUES (102, recording yangfeng, 20)

INSERT INTO tab2 VALUES (101 and 13761512841)

INSERT INTO tab2 VALUES (102 Magnum 13563258514)

Create a view to connect two tables

CREATE VIEW tab_view AS

SELECT tab1.tid,tname,ttel,tadr FROM tab1,tab2

WHERE tab1.tid = tab2.tid

-- create a trigger

CREATE OR REPLACE TRIGGER tab_trigger

INSTEAD OF INSERT ON tab_view

BEGIN

INSERT INTO tab1 (tid,tname) VALUES (: new.tid,:new.tname)

INSERT INTO tab2 (ttel,tadr) VALUES (: new.ttel,:new.tadr)

END

/

You can now use the view to insert data

INSERT INTO tab_view VALUES (105Zhaoyangzhengzhong 13886681288Med beiding)

-- View the effect

SELECT * FROM tab_view

Example 3Mel-create a trigger to compare the updated wages in the emp table

CREATE OR REPLACE TRIGGER sal_emp

BEFORE UPDATE ON emp

FOR EACH ROW

BEGIN

IF: OLD.sal >: NEW.sal THEN

DBMS_OUTPUT.PUT_LINE ('wage reduction')

ELSIF: OLD.sal <: NEW.sal THEN

DBMS_OUTPUT.PUT_LINE ('salary increase')

ELSE

DBMS_OUTPUT.PUT_LINE ('wages have not changed')

END IF

DBMS_OUTPUT.PUT_LINE ('salary before renewal:' | |: OLD.sal)

DBMS_OUTPUT.PUT_LINE ('updated salary:' | |: NEW.sal)

END

/

-- perform UPDATE to view the results

UPDATE emp SET sal = 3000 WHERE empno = '7788'

Example 4Mel-create a trigger to store the operation CREATE and DROP in the log_ information table

-- create tables

CREATE TABLE log_info

(

Manager_user VARCHAR2 (15)

Manager_date VARCHAR2 (15)

Manager_type VARCHAR2 (15)

Obj_name VARCHAR2 (15)

Obj_type VARCHAR2 (15)

);

-- create a trigger

CREATE OR REPLACE TRIGGER trig_log_info

AFTER CREATE OR DROP ON SCHEMA

BEGIN

INSERT INTO log_info

VALUES (USER,SYSDATE,SYS.DICTIONARY_OBJ_NAME,SYS.DICTIONARY_OBJ_OWNER

SYS.DICTIONARY_OBJ_TYPE)

END

/

-- Test statement

CREATE TABLE a (id NUMBER)

CREATE TYPE aa AS OBJECT (id NUMBER)

/

DROP TABLE a

DROP TYPE aa

-- View the effect

SELECT * FROM log_info

SELECT * FROM USER_TRIGGERS

SELECT * FROM ALL_TRIGGERS

SELECT * FROM DBA_TRIGGERS;-you must log in as DBA to use this data dictionary

-- enable and disable

ALTER TRIGGER trigger_name DISABLE

ALTER TRIGGER trigger_name ENABLE

This is the answer to the question about ORACLE trigger syntax and case analysis. I hope the above content can be of some help to you. If you still have a lot of doubts to be solved, you can follow the industry information channel to learn more about it.

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.