数据库技术学习 https://www.itkc8.com
行级DML触发器
每当一条记录出现更新操作时进行触发操作定义时要定义FOR EACH ROW
使用":old.字段"和":new.字段"标识符
No. | 触发语句 | :old字段 | :new字段 |
1 | INSERT | 未定义,字段内容为NULL | INSERT操作结束后,为增加数据值 |
2 | UPDATE | 更新数据前的原始值 | UPDATE操作之后,更新数据后的新值 |
3 | DELETE | 删除前的原始值 | 未定义,字段内容均为NULL |
":old.字段"和":new.字段只对行级触发有效,如果触发器没有定义FOR EACH RO则无效而报语法错误
示例一、增加员工信息时,其职位必须在已经职位内选择,并且工资不能超过5000,入职日期为当前日期
--创建触发器
create or replace trigger myempinsert
before insert on myemp
for each ROW
declare
v_date DATE;
v_jobcount NUMBER;
BEGIN
SELECT COUNT(empno) INTO v_jobcount FROM myemp WHERE:new.job IN(SELECT DISTINCT job FROM myemp);
IF v_jobcount=0 THEN --没有找到此职位
raise_application_error(-20009,'职位错误');
ELSE
IF :new.Sal>5000 THEN
raise_application_error(-20003,'工资不能超过5000');
END IF;
END IF;
:new.Hiredate:=SYSDATE;
end myempinsert;
--调用执行
DECLARE
BEGIN
--添加错误信息
INSERT INTO myemp(empno,ename,job,mgr,sal,deptno)VALUES(9999,'Bdqn','MNAGER',7788,8000,10);
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line(SQLERRM);
END;
示例二、myemp工资涨幅不能超过10%
--创建触发器
CREATE OR REPLACE TRIGGER MYEMPADDSAL_TRIGGER
BEFORE UPDATE ON MYEMP
FOR EACH ROW
DECLARE
BEGIN
IF ABS((:NEW.SAL - :OLD.SAL) / :OLD.SAL) > 0.1 THEN
raise_application_error(-20005,'工资最大涨幅不能超过10%');
END IF;
END MYEMPADDSAL_TRIGGER;
--调用
DECLARE
BEGIN
--添加错误信息
--INSERT INTO myemp(empno,ename,job,mgr,sal,deptno)VALUES(9999,'Bdqn','MNAGER',7788,8000,10);
UPDATE myemp SET sal=3000 WHERE empno=7369;
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line(SQLERRM);
END;
示例三、使用序列
--创建表
CREATE TABLE mt(
ID NUMBER,
NAME VARCHAR2(30),
address varchar2(50),
CONSTRAINT pd_mtid PRIMARY KEY(ID)
);
SELECT * FROM mt;
--创建序列
CREATE SEQUENCE mt_se;
--创建触发器
create or replace trigger mt_trigger
before insert on mt
for each row
declare
-- local variables here
begin
SELECT mt_se.NEXTVAL INTO :new.id FROM dual;
end mt_trigger;
--插入数据
INSERT INTO mt(name,address)VALUES('张三','深圳');
INSERT INTO mt(name,address)VALUES('李四','深圳');
SELECT * FROM mt;
--发现ID自动增加
12c自动序列
oracle12C中可以用以下写法:更直观
--创建表
CREATE TABLE mt(
ID NUMBER,
NAME VARCHAR2(30),
address varchar2(50),
CONSTRAINT pd_mtid PRIMARY KEY(ID)
);
CREATE TABLE mtme AS SELECT * FROM mt WHERE 1=2;
SELECT * FROM mt;
SELECT * FROM mtme;
--创建序列
CREATE SEQUENCE mt_se;
--创建触发器
create or replace trigger mtme
before insert on mtme
for each row
declare
BEGIN
DELETE mtme;
INSERT INTO mt(id,name,address)VALUES(mt_se.nextval,:NEW.NAME,:NEW.ADDRESS);
end mtme;
--插入数据
INSERT INTO mt(name,address)VALUES('张三','深圳');
INSERT INTO mt(name,address)VALUES('李四','深圳');