返回
数据
分类

A表数据有更新、删除、插入时,'错误数据'END AS AFTER

日期: 2020-01-02 08:13 浏览次数 : 134

MySQLcasewhen条件分支判断

举例说明

SELECT
    CASE a.AFTER_QUALITY
WHEN '0' THEN
    '过免维期'
WHEN '1' THEN
    '未过免维期'
ELSE
    '错误数据'
END AS AFTER_QUALITY
FROM
    t_maintain_log a

数据库表内容:
必赢手机登录网址 1
运行结果:
必赢手机登录网址 2

举例说明 SELECT CASE a.AFTER_QUALITYWHEN '0' THEN '过免维期'WHEN '1' THEN '未过免维期'ELSE '错误数据'END AS AFTER_QUALITYFROM t_mai...

db2使用版本9.7

创建A 、B两个表,A表数据有更新、删除、插入时,将A表ID记录放入B表

1.DELETE TRIGGER,used for delete employer after delete the department. This is a rows trigger.

1、create table A (id varchar(5),name varchar(30));

CREATE OR REPLACE TRIGGER del_eptid
AFTER DELETE ON deptment
FOR EACH ROW--Rows trigger
BEGIN
DELETE FROM emp WHERE id=:old.id;
必赢手机登录网址 ,END del_deptid;
/

   create table B (id varchar(5),name varchar(30))

2.INSERT TRIGGER,used for insert a new employer after create a new department.This is a rows trigger.

2、创建触发器

CREATE OR REPLACE TRIGGER insert_dept
AFTER INSERT ON deptment
FOR EACH ROW
BEGIN
INSERT INTO emp(eid,ename,id) values('121','QWERT',:new.id);
END
;
/

--插入时触动

3.UPDATE Trigger. Used for update employer's department after update the department id.

CREATE TRIGGER administrator.tri_insert 

CREATE OR REPLACE TRIGGER update_dept
AFTER UPDATE ON deptment
FOR EACH ROW --Rows trigger.
BEGIN
UPDATE emp SET id=:new.id WHERE id=:old.id;
END;
/

AFTER INSERT ON administrator.A

4.DELETE TRIGGER,for a special row,not allowed to delete,raise a error.

REFERENCING NEW AS N 

CREATE OR REPLACE TRIGGER book_delete
AFTER DELETE ON books
FRO EACH ROW
BEGIN
IF :old.books_id='0001' THEN
RAISE_APPLICATION_ERROR(-20000,'Not allow delete!');
END IF;
END;
/

FOR EACH ROW MODE DB2SQL

5.语句级触发器。当一个操作发生时,记录相关操作。

BEGIN ATOMIC

CREATE OR REPLACE TRIGGER dml_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;
/

    INSERT INTO administrator.B(ID,NAME) VALUES(N.ID,N.NAME );

6.ROWS TRIGGER.

END   

--此事例用于给AUTO表的NUMBER列一数字。

--测试: INSERT INTO  administrator.A VALUES ('3','Name');

CREATE OR REPLACE TRIGGER set_no
BEFORE INSERT ON auto
FOR EACH ROW
DECLARE 
sn number(5);
BEGIN
SELECT myseq.nextval INTO sn FROM dual;
:NEW.a:=sn;
END;
/

 

7.--替换触发器
--解决视图多表更新问题,而且替换触发器只能建立于视图之上
--SHOW ERROR 用于显示触发器的错误
CREATE OR REPLACE TRIGGER tr_v_e_d
INSTEAD OF INSERT ON v_emp_dept
FOR EACH ROW
BEGIN
INSERT INTO deptment VALUES(:new.id,:new.name);
INSERT INTO emp(edi,ename,sex,id) VALUES(:new.eid,:new.ename,:new.sex,:new.id);
END;
/

--删除时触动

CREATE TRIGGER administrator.tri_delete

AFTER DELETE ON administrator.A

REFERENCING OLD AS O 

FOR EACH ROW MODE DB2SQL

BEGIN ATOMIC

    INSERT INTO administrator.B(ID) VALUES(O.ID);

END --测试:DELETE FROM administrator.A WHERE ID = '3' 

 

--更新时触动 

CREATE OR REPLACE  TRIGGER administrator.tri_update

AFTER UPDATE OF NAME ON administrator.A

REFERENCING NEW AS N OLD AS O 

FOR EACH ROW MODE DB2SQL

BEGIN ATOMIC

    INSERT INTO administrator.B(ID,NAME) VALUES(O.ID,N.NAME);

END  

--测试:UPDATE administrator.A SET NAME = 'n2_name'  

 

--有资料提示 DB2 9.7以上版本支持 AFTER INSERT OR DELETE OR UPDATE 写法,可是9.7版本并不支持

例子:

  CREATE OR REPLACE TRIGGER HIRED
   AFTER INSERT OR DELETE OR UPDATE OF SALARY ON EMPLOYEE
   REFERENCING NEW AS N OLD AS O FOR EACH ROW
   BEGIN
      IF INSERTING THEN 

  UPDATE COMPANY_STATS SET NBEMP = NBEMP + 1;

      ELSEIF 

  DELETING THEN UPDATE COMPANY_STATS SET NBEMP = NBEMP - 1;

      ELSEIF (UPDATING AND (N.SALARY > 1.1 * O.SALARY))
         THEN SIGNAL SQLSTATE '75000' SET MESSAGE_TEXT='Salary increase>10%';
      END IF;
   END; 

 

之后,考虑使用DB2中ORACLE兼容性的设置,db2set DB2_COMPATIBILITY_VECTOR=ORA , 依然不支持 AFTER INSERT OR DELETE OR UPDATE 写法,有成功在9.7版本中成功使用AFTER INSERT OR DELETE OR UPDATE 写法的麻烦指导下