create or replace trigger AutoUpdateStatus after insert OF AAA on 表名 REFERENCING OLD AS OLD NEW AS NEW FOR EACH ROW declare V_AAA varchar2(255); V_ID number(20); begin V_AAA := :new.AAA; V_ID := :new.表ID;
if SUBSTR(V_AAA,1,1) = '3' then update 表名 set Status = 'N' WHERE 表ID=V_ID; ELSE update 表名 set Status = 'Y' WHERE 表ID=V_ID; END IF;end AutoUpdateStatus;
CREATE OR REPLACE TRIGGER <trigger_name> <BEFORE | AFTER> <ACTION> ON <table_name> DECLARE <variable definitions> BEGIN <trigger_code> EXCEPTION <exception clauses> END [<trigger_name>];
create or replace trigger AutoUpdateStatus after insert OF AAA on 表名 REFERENCING OLD AS OLD NEW AS NEW FOR EACH ROW declare V_AAA varchar2(255); V_ID number(20); begin V_AAA := :new.AAA; V_ID := :new.表ID;
if SUBSTR(V_AAA,1,1) = '3' then update 表名 set Status = 'N' WHERE 表ID=V_ID; ELSE update 表名 set Status = 'Y' WHERE 表ID=V_ID; END IF;end AutoUpdateStatus;
本机测试通过 create or replace trigger abc before insert on table1 for each row begin if substr(:new.aaa, 1, 1) = '3' then :new.status := 'N'; else :new.status := 'Y'; end if; end;
CREATE OR REPLACE TRIGGER TRG_JAY_TEST BEFORE INSERT ON JAY_TEST_TRIGGER REFERENCING OLD AS OLD NEW AS NEW FOR EACH ROW DECLARE V_ID VARCHAR2(10); V_NAME VARCHAR2(10); BEGIN V_ID := :NEW.ID; V_NAME := :NEW.NAME; IF TRIM(V_NAME) LIKE 'B%' THEN UPDATE JAY_TEST_TRIGGER SET STATUS = 'Y' WHERE ID = V_ID; END IF; END TRG_JAY_TEST;====================================== 這樣子為什麼一點反應也沒有呀
CREATE OR REPLACE TRIGGER TRG_JAY_TEST AFTER INSERT ON JAY_TEST_TRIGGER REFERENCING OLD AS OLD NEW AS NEW FOR EACH ROW DECLARE V_ID VARCHAR2(10); V_NAME VARCHAR2(10); BEGIN V_ID := :NEW.ID; V_NAME := :NEW.NAME; IF TRIM(V_NAME) LIKE 'B%' THEN UPDATE JAY_TEST_TRIGGER SET STATUS = 'Y' WHERE ID = V_ID; END IF; END TRG_JAY_TEST; ============================== 報ORA-04091錯誤,和上面就一個Before,一個after區別而已
CREATE OR REPLACE TRIGGER TRG_JAY_TEST AFTER INSERT ON JAY_TEST_TRIGGER FOR EACH ROW DECLARE V_ID VARCHAR2(10); V_NAME VARCHAR2(10); BEGIN V_ID := :NEW.ID; V_NAME := :NEW.NAME; IF TRIM(V_NAME) LIKE 'B%' THEN UPDATE JAY_TEST_TRIGGER SET STATUS = 'Y' WHERE ID = V_ID; END IF; END TRG_JAY_TEST;
create or replace trigger abc before insert on table1 for each row begin if substr(:new.aaa, 1, 1) = '3' then :new.status := 'N'; else :new.status := 'Y'; end if; end; ============================== 這樣子是可以,但是我想知道:我那樣子這什麼不行啊
CREATE OR REPLACE TRIGGER TRG_JAY_TEST AFTER INSERT ON JAY_TEST_TRIGGER FOR EACH ROW DECLARE V_ID VARCHAR2(10); V_NAME VARCHAR2(10); BEGIN V_ID := :NEW.ID; V_NAME := :NEW.NAME; IF TRIM(V_NAME) LIKE 'B%' THEN UPDATE JAY_TEST_TRIGGER SET STATUS = 'Y' WHERE ID = V_ID; END IF; END TRG_JAY_TEST; 你写的这个有点语法错误和逻辑错误.. 语法错误如下 TRIM(V_NAME) LIKE 'B%'这么写不对.应该这么写 if substr(trim(v_name),1,1)='B' then 逻辑错误如下 UPDATE JAY_TEST_TRIGGER SET STATUS = 'Y' WHERE ID = V_ID; 如果有重复的id的话不是把前面的值都盖了嘛..
UPDATE JAY_TEST_TRIGGER SET STATUS = 'Y' WHERE ID = V_ID; 如果有重复的id的话不是把前面的值都盖了嘛.. ===================================== 我的ID是主鍵,不可能重複的,現在,關鍵Trigger它什麼事情也沒有做,不知道為什麼?
create or replace trigger AutoUpdateStatus after
insert OF AAA on 表名 REFERENCING OLD AS OLD NEW AS NEW FOR EACH ROW
declare
V_AAA varchar2(255);
V_ID number(20);
begin
V_AAA := :new.AAA;
V_ID := :new.表ID;
if SUBSTR(V_AAA,1,1) = '3' then
update 表名 set
Status = 'N'
WHERE 表ID=V_ID;
ELSE
update 表名 set
Status = 'Y'
WHERE 表ID=V_ID;
END IF;end AutoUpdateStatus;
<BEFORE | AFTER> <ACTION>
ON <table_name>
DECLARE
<variable definitions>
BEGIN
<trigger_code>
EXCEPTION
<exception clauses>
END [<trigger_name>];
insert OF AAA on 表名 REFERENCING OLD AS OLD NEW AS NEW FOR EACH ROW
declare
V_AAA varchar2(255);
V_ID number(20);
begin
V_AAA := :new.AAA;
V_ID := :new.表ID;
if SUBSTR(V_AAA,1,1) = '3' then
update 表名 set
Status = 'N'
WHERE 表ID=V_ID;
ELSE
update 表名 set
Status = 'Y'
WHERE 表ID=V_ID;
END IF;end AutoUpdateStatus;
create or replace trigger abc
before insert on table1
for each row
begin
if substr(:new.aaa, 1, 1) = '3' then
:new.status := 'N';
else
:new.status := 'Y';
end if;
end;
BEFORE INSERT ON JAY_TEST_TRIGGER
REFERENCING OLD AS OLD NEW AS NEW
FOR EACH ROW
DECLARE
V_ID VARCHAR2(10);
V_NAME VARCHAR2(10);
BEGIN
V_ID := :NEW.ID;
V_NAME := :NEW.NAME;
IF TRIM(V_NAME) LIKE 'B%' THEN
UPDATE JAY_TEST_TRIGGER SET STATUS = 'Y' WHERE ID = V_ID;
END IF;
END TRG_JAY_TEST;======================================
這樣子為什麼一點反應也沒有呀
AFTER INSERT ON JAY_TEST_TRIGGER
REFERENCING OLD AS OLD NEW AS NEW
FOR EACH ROW
DECLARE
V_ID VARCHAR2(10);
V_NAME VARCHAR2(10);
BEGIN
V_ID := :NEW.ID;
V_NAME := :NEW.NAME;
IF TRIM(V_NAME) LIKE 'B%' THEN
UPDATE JAY_TEST_TRIGGER SET STATUS = 'Y' WHERE ID = V_ID;
END IF;
END TRG_JAY_TEST;
==============================
報ORA-04091錯誤,和上面就一個Before,一個after區別而已
你改成我那么写不就哦了吗?不正好是你要的效果嘛..
AFTER INSERT ON JAY_TEST_TRIGGER
FOR EACH ROW
DECLARE
V_ID VARCHAR2(10);
V_NAME VARCHAR2(10);
BEGIN
V_ID := :NEW.ID;
V_NAME := :NEW.NAME;
IF TRIM(V_NAME) LIKE 'B%' THEN
UPDATE JAY_TEST_TRIGGER SET STATUS = 'Y' WHERE ID = V_ID;
END IF;
END TRG_JAY_TEST;
before insert on table1
for each row
begin
if substr(:new.aaa, 1, 1) = '3' then
:new.status := 'N';
else
:new.status := 'Y';
end if;
end;
==============================
這樣子是可以,但是我想知道:我那樣子這什麼不行啊
AFTER INSERT ON JAY_TEST_TRIGGER
FOR EACH ROW
DECLARE
V_ID VARCHAR2(10);
V_NAME VARCHAR2(10);
BEGIN
V_ID := :NEW.ID;
V_NAME := :NEW.NAME;
IF TRIM(V_NAME) LIKE 'B%' THEN
UPDATE JAY_TEST_TRIGGER SET STATUS = 'Y' WHERE ID = V_ID;
END IF;
END TRG_JAY_TEST;
你写的这个有点语法错误和逻辑错误..
语法错误如下
TRIM(V_NAME) LIKE 'B%'这么写不对.应该这么写
if substr(trim(v_name),1,1)='B' then
逻辑错误如下
UPDATE JAY_TEST_TRIGGER SET STATUS = 'Y' WHERE ID = V_ID;
如果有重复的id的话不是把前面的值都盖了嘛..
UPDATE JAY_TEST_TRIGGER SET STATUS = 'Y' WHERE ID = V_ID;
如果有重复的id的话不是把前面的值都盖了嘛..
=====================================
我的ID是主鍵,不可能重複的,現在,關鍵Trigger它什麼事情也沒有做,不知道為什麼?