Oracle??????????????洢???????????????
???????????? ???????[ 2016/10/10 9:53:53 ] ????????????? Oracle
???????????????????trigger??
???????????????
????http://wenku.baidu.com/link?url=brtd9myO4XIaEjRPaRB0yWgV2_TfXmNxS6KKjB_avq7d0hPs3SknJdrs-JLtWe–H-f3q-I61HUxkcqqAFMC0z6Zf65QBbmFRB8TAlULmGS
????http://www.cnblogs.com/friends-wf/p/3793417.html?洢???????
????http://blog.csdn.net/indexman/article/details/8023740/??????????????????????????!!??
??????????????????洢?????????????С?????????????????У????????????????????????????????С?????????????????????????????д??????д???????oracle???????????????????insert??update??delete????????????????????????oracle???????????????????????oracle????????????????????????????????????????????????????????????????????????????????????????????????????????????????
??????????????
????create [or replace] trigger ????????
????before|after|instead of ?????? on ????|?????|database|?????.schema
????[for each row [when]]
????PL/SQL??|???ù???(call procedure_name)
????or replace????????????????????????????????滻
????before|after?????????????
????instead of???滻??????????????м???????
??????????????????????????????????????????
????on?????????????????????
????for each row [when]???????????????????????δ?????
????PL/SQL??|???ù???(call procedure_name)????????????????е????
??????????????
?????????????????????
????before??????????????????????????д???????
????create [or replace] trigger ????????
????before insert|delete|update on ????
????before create|drop|alter on database|?????.schema
????[for each row [when]]
????PL/SQL??|???ù???(call procedure_name)
?????м????????????????????????????????ò?????????????????????????????????Ρ??????for each row??
????create [or replace] trigger ????????
????before|after|instead of insert|delete|update on ????|?????
????for each row [when]
????PL/SQL??|???ù???(call procedure_name)
???????м?????????PL/SQL???when????п????????????????????????????????????????????new??old??????????PL/SQL???????????????????????????????e??(:)??????when?????????e???
?????????????????????????????洢??????洢?????????????????洢????????????????????ò???У???????????????????????????С??????????????????????????????????С???????????????????????????????д??????д???????firing????ORACLE??????????????????е?INSERT??UPDATE??DELETE????????????????????????ORACLE???????????????????????ORACLE?????????????????????????????????????????????????????????????????????????????????????????????????????????????????
????1 DML??????
????ORACLE??????DML?????д???????????DML??????????????д?????????????????л???????????д?????
????2 ?????????
??????????ORACLE?????????????????????????????????в?????????????????????????????ORACLE 8????????????????????????????
????3 ????????
????ORACLE 8i ???????????????????????????????????????ORACLE?????????????н??д???????ORACLE?????????????
??????伶??????????????????????????????????????Ρ?δ???for each row??
????create [or replace] trigger ????????
????before|after insert|delete|update on ????|?????
????before|after create|drop|alter on database|?????.schema
????PL/SQL??|???ù???(call procedure_name)
?????м??????????м?????????DML?????????????????Ρ?
??????伶???????????DML????????Σ??????TABLE???в?????????500?У??????????????伶????????????Σ????м????????????500?????
???????????????????м???????????????????伶????????????????????????????????
????1?? ????????а???FOR EACH ROW??? www.2cto.com
????2?? ??BEFORE……FOR EACH ROW???????У????????????????????????
????DML??????
????????DML??????
????????????????????????????????????????????????????????????????????????????????????????????д????????????????
????DML????????????
????l CREATE TRIGGER?????????????????????32KB??
????l ???????????SELECT ???????SELECT … INTO …??????????????α???????SELECT ???
????l ???????в????????????????????? COMMIT; ROLLBACK?? SVAEPOINT ???
????l ???????????????????????????????????????????
????l ???????в??????LONG?? LONG RAW ?????
????l ??????????????LOB ?????е??????????????? :NEW ???LOB???е??????
????DML?????????????
????l ?????????????????????????????????BEFORE???????????DML??????????????????Щ?????????????????Щ?????????????AFTER???????????DML?????????????????ò????????Щ?o????
????l ????????????????????????????????DML??????INSERT??UPDATE??DELETE???????????????????????????????????????????????????OR??????????????AND?????????
????l ????ν?????????????а???????????????INSERT??UPDATE??DELETE????????????????????????????в??????????????ORACLE????????????ν???
????1????INSERTING?????????????INSERT??????TRUE???????FALSE??
????2????UPDATING [??column_1??column_2??…??column_x??]?????????????UPDATE ???????????column_x?У??????TRUE???????FALSE??????column_x???????
????3????DELETING?????????????DELETE????????TRUE???????FALSE??
??????????????????????????????????????
????l ?????????????伶?????м?????????
????l ????????????WHEN??????????????????????????м?????????????????????????UPDATING??????е??б??
????instead of ??????:??????????DML??????????????п?????????????????????????????????????????μ????????????????????и????
????create [or replace] trigger ????????
????instead of insert|delete|update on ?????
????[for each row [when]]
????PL/SQL??|???ù???(call procedure_name)
????????????????????????????????with check option???;
????for each row????????????instead of????????????м????????????????м?????????
??????б?????;
??????б?????????????????????instead of??????????????DML?????????????
?????????????:
????l ?????????????????????????????? ???磺DML???(INSERT?? UPDATE?? DELETE??????????????????????)??DDL?????CREATE??ALTER??DROP???????????д??????????????????????????????????????????????????????????????????????????????
????l ???????????TRIGGER ??????????????????BEFORE?????????(AFTER)??????????????????TRIGGER ????????
????l ??????????????TRIGGER ????????????????????????????????????????顣 ???磺PL/SQL ?顣
????l ????????????????????????????????????Щ????????????????????????????????????д?????????
????l ????????????WHEN????????????????????е??????????TRUE??????????????????????д????????????д?????????
????l ??????????????????????????????е??????????伶(STATEMENT)?????????м?(ROW)????????
??????伶(STATEMENT)?????????????????????????????????????????Σ?
?????м?(ROW)??????????????????????????????????ò?????????????????????????????????Ρ?
??????д??????????????????????
????l ?????????????????
????l ???????????12???????????????????????????????????????????????????????????????ì???
????l ???????????????????????????DML????????????????
????l ?????????32KB???????????????????????????????????????CALL?????е??á?
????l ???????????в????????DML???SELECT??INSERT??UPDATE??DELETE???????????DDL???CREATE??ALTER??DROP????
????l ???????в????????????????(COMMIT??ROLLBACK??SAVEPOINT)????????????????????????????????????????????????????????????????
????l ????????????е?????κι?????????????????????????????
????l ????????????в????????κ?Long??blob?????????new????old?????????е??κ?long??blob?С?
????l ?????????????(??DML????????INSTEAD OF????????????????)??????????????н??????
??????scott????£?????????????????
????Sql????
????-- ??伶??????
????-- ????????????????????
????create or replace trigger tr_sec_emp
????-- before:??DML????????
????before insert or update or delete on emp
????-- ??????????????????м???????伶????????????? FOR EACH STATEMENT
????begin
????if to_char(sysdate??'DAY'??'nls_date_language=AMERICAN')
????in('SAT'??'SUN') then
????case
????-- ???????????Insert?????????????ν???????True???????False
????when inserting then
????raise_application_error(-20001??'??????????????????????');
????-- ???????????Update?????????????ν???????True???????False
????when updating then
????raise_application_error(-20002??'?????????????????????');
????-- ???????????Delete?????????????ν???????True???????False
????when deleting then
????raise_application_error(-20003??'??????????????????????');
????end case;
????end if;
????end;
????/
??????
???·???
??????????????????
2023/3/23 14:23:39???д?ò??????????
2023/3/22 16:17:39????????????????????Щ??
2022/6/14 16:14:27??????????????????????????
2021/10/18 15:37:44???????????????
2021/9/17 15:19:29???·???????·
2021/9/14 15:42:25?????????????
2021/5/28 17:25:47??????APP??????????
2021/5/8 17:01:11