--手办表
DROP TABLE T_GIRL;
CREATE TABLE T_GIRL(
ID NUMBER,
NAME VARCHAR2(30),
PRIMARY KEY(ID),
AGE NUMBER,
TCODE NUMBER
);
--数量表
DROP TABLE T_SQL_LOG;
CREATE TABLE T_SQL_LOG(
TID NUMBER ,
TNAME VARCHAR2(30),
TCODE VARCHAR(20),
TCOUNT NUMBER
);
--手办型号表
DROP TABLE T_T_GIRL;
CREATE TABLE T_T_GIRL(
TTName varchar2(10),
TTCODE NUMBER
);
-- 触发器案例1:
--当插入一条新数据,副表添加一条记录,TCOUNT为0,
--添加重复的数据(标识为NAME),不添加记录,Tcount +1
CREATE OR REPLACE TRIGGER TR_GIRL_2
BEFORE INSERT ON T_GIRL
FOR EACH ROW
DECLARE
v_count NUMBER;
BEGIN
--查询主表新增手办对应的数量(where tname=:new.name),并赋值给参数v_count(count(*) into v_count)
SELECT COUNT(*) INTO v_count
FROM T_SQL_LOG
WHERE TNAME = :NEW.NAME;
--判断是否是新增的手办,如果是向T_SQL_LOG(手办数量表)添加一条新手办的数量信息(count=1,tcode=:new.tcode,tname=:new.name)
--如果不是新增的手办,则对应数量表数量+1(tcount+1)
IF v_count > 0 THEN
UPDATE T_SQL_LOG
SET TCOUNT = TCOUNT + 1
WHERE TNAME = :NEW.NAME;
ELSE
INSERT INTO T_SQL_LOG (TID, TNAME, TCODE, TCOUNT)
VALUES (:NEW.ID, :NEW.NAME, :NEW.TCODE, 1);
END IF;
END;
--SELECT 查询出来的字段 INTO 需要被赋值的参数 FROM 查询的表名 where 条件
--触发器:案例二
--添加数据时,如果TCOUNT为空,那就去T_T_GIRL表中找,对应关系是NAME-TTNAME
--如果没有就在T_T_GIRL添加一条数据与之对应,name=:new.name,ttcode=:new.id||1001
CREATE OR REPLACE TRIGGER TR_GIRL_3
BEFORE INSERT ON T_GIRL
FOR EACH ROW
DECLARE
--V-COUNT NUMBER;
BEGIN
IF :NEW.TCODE IS NULL THEN
BEGIN
-- 查询 T_T_GIRL 表中是否存在与 :NEW.NAME 匹配的 TTName,如果有,则将新数据的TCODE(:NEW.TCODE)替换成查询出来的值
--SELECT TTCODE INTO V-CODE FROM T_T_GIRL WHERE TTNAME=:NEW.NAME;
SELECT TTCODE INTO :NEW.TCODE FROM T_T_GIRL WHERE TTNAME=:NEW.NAME;
--:NEW.TCODE := v_TTCODE;
-- 如果找不到对应的数据,插入新数据
EXCEPTION WHEN OTHERS THEN
INSERT INTO T_T_GIRL (TTName, TTCODE) VALUES (:NEW.NAME, :NEW.ID || 1001);
--:NEW.TCODE := :NEW.ID || 1001; -- 更新 :NEW.TCODE
SELECT TTCODE INTO :NEW.TCODE FROM T_T_GIRL WHERE TTNAME=:NEW.NAME;
END;
END IF;
END;
标签:触发器,NAME,--,NUMBER,TCODE,Oracle,NEW,GIRL From: https://www.cnblogs.com/Yytan-BK/p/17650674.html