有个表 tb1,有字段 f1,f2,要求当插入一行到表时,如果f2字段为空或者null,则付给他一个默认值'aaa',大体形式如下,CREATE OR REPLACE TRIGGER trg_tb1
AFTER INSERT ON tb1
if inserting then
if :new.f2='' or :new.f2 is null then
:new.f2='aaa';
end if;
END trg_tb1;意思是表明了,可就是不知道语法格式,请大侠们指正,谢谢。
AFTER INSERT ON tb1
if inserting then
if :new.f2='' or :new.f2 is null then
:new.f2='aaa';
end if;
END trg_tb1;意思是表明了,可就是不知道语法格式,请大侠们指正,谢谢。
2 BEFORE insert on tb1
3 for each row
4 declare
5 -- local variables here
6 begin
7 IF :new.f2 IS NULL THEN
8 SELECT 'aaa' INTO :new.f2 FROM dual;
9 END IF;
10 end trg_tb1;
11 /
Trigger created
SQL> insert into tb1 values('a',null);
1 row inserted
SQL> select * from tb1;
F1 F2
---------- ----------
a aaa
SQL>
before INSERT ON tb1
for each rowbegin
if inserting then
if :new.f2 is null then
:new.f2 := 'aaa';
end if;
insert into tb1 values (:new.f1, :new.f2);
commit;
end if;
END;
decode(f2,null,'aaa')
或是创建表时该字段default植为 aaa
before INSERT ON tb1
for each rowbegin
if inserting then
if :new.f2 is null then
:new.f2 := 'aaa';
end if;
end if;
END;
2 for each row
3 begin
4 if :new.f2 is null or :new.f2=' ' then
5 :new.f2:='aaa';
6 end if;
7 end;
8 /触发器已创建
1* insert into tb1 values(1,'')
SQL> /已创建 1 行。
SQL> select * from tb1; F1 F2
---------- ----------
1 aaa
SQL> select * from salgrade; GRADE LOSAL HISAL
---------- ---------- ----------
6
1 700 1200
2 1201 1400
3 1401 2000
4 2001 3000
5 3001 9999已选择6行。
SQL> select * from salgrade where losal is null; GRADE LOSAL HISAL
---------- ---------- ----------
6
SQL> select * from salgrade where losal='';未选定行
一个是
1 - filter("LOSAL" IS NULL)一个是
1 - filter(NULL IS NOT NULL)