create or replace trigger t_table_triger
after insert on t_table
for each row
declare
msg varchar2(22);
returnMsg varchar2(180);
xsBH varchar2(10);
sourceUserTpCount number:=0;
begin
if inserting then --inserting表示插入操作,每种操作类型都有对应的关键字
msg:=:NEW.msg; --获取触发事件插入的数据值
msg:=Replace(msg,'+','');--变量赋值
msg:=Replace(msg,'-','');--替换,跟MSSQL里函数相同
if LOWER(SUBSTR(msg,1,1))='v' and LENGTH(msg)>3 then
if sysdate<to_date('2009-12-26','yyyy-MM-dd') then --时间比较,sysdate是当前时间
select count(*) into sourceUserTpCount from t_siteuser where userid=sourceUser and TO_CHAR(gentime,'YYYYMMDD')= TO_CHAR(sysdate,'YYYYMMDD');
if sourceUserTpCount<=20 then
dbms_output.put_line(xsBH);--打印信息
insert into t_siteuser(id,bh,gentime) values (SEQ_t_siteuser.nextval,xsBH,sysdate);--SEQ_t_siteuser.nextval获取标识ID
returnMsg:='感谢您对'||xsBH||'号的支持!';--字符串与变量结合
else
returnMsg:='对不起,网站每天限投20票!';
end if;
else
returnMsg:='活动已结束,欢迎您继续关注!';
end if ;
end if;
end if;
end;
after insert on t_table
for each row
declare
msg varchar2(22);
returnMsg varchar2(180);
xsBH varchar2(10);
sourceUserTpCount number:=0;
begin
if inserting then --inserting表示插入操作,每种操作类型都有对应的关键字
msg:=:NEW.msg; --获取触发事件插入的数据值
msg:=Replace(msg,'+','');--变量赋值
msg:=Replace(msg,'-','');--替换,跟MSSQL里函数相同
if LOWER(SUBSTR(msg,1,1))='v' and LENGTH(msg)>3 then
if sysdate<to_date('2009-12-26','yyyy-MM-dd') then --时间比较,sysdate是当前时间
select count(*) into sourceUserTpCount from t_siteuser where userid=sourceUser and TO_CHAR(gentime,'YYYYMMDD')= TO_CHAR(sysdate,'YYYYMMDD');
if sourceUserTpCount<=20 then
dbms_output.put_line(xsBH);--打印信息
insert into t_siteuser(id,bh,gentime) values (SEQ_t_siteuser.nextval,xsBH,sysdate);--SEQ_t_siteuser.nextval获取标识ID
returnMsg:='感谢您对'||xsBH||'号的支持!';--字符串与变量结合
else
returnMsg:='对不起,网站每天限投20票!';
end if;
else
returnMsg:='活动已结束,欢迎您继续关注!';
end if ;
end if;
end if;
end;
---------------------------------------------------------- --每进行一次交易,就要调用触发器,自动扣除或增加账户金额 ---------------------------------------------------------- create table account ( customerName varchar2(30) primary key, cardID varchar2(8), currentMoney number ); insert into account values('Daivd','10010001',5000); insert into account values('Jason','10010002',3000); create table trans ( transDate date, cardID varchar2(8), transType varchar2(10), transMoney number ); insert into trans values(sysdate,'10010001','取款',1000); create or replace trigger trans_trigger before insert on trans for each row declare v_currentMoney account.currentMoney%type; begin --判断类型 if :new.transType='取款' then --取款 select currentMoney into v_currentMoney from account where cardID=:new.cardID; if v_currentMoney < :new.transMoney then raise_application_error(-20001,'余额不足'); end if; update account set currentMoney=currentMoney-:new.transMoney where cardID=:new.cardID; else --存款 update account set currentMoney=currentMoney+:new.transMoney where cardID=:new.cardID; end if; exception when no_data_found then raise_application_error(-20002,'无效的帐户'); end; --模式(schema)级触发器 create or replace trigger schema_trigger before drop on schema begin dbms_output.put_line('schema_trigger called'); dbms_output.put_line(ora_dict_obj_name); dbms_output.put_line(ora_dict_obj_type); if ora_dict_obj_name='ACCOUNT' then raise_application_error(-20003,'ACCOUNT表不能被删除'); end if; end; drop table account; --ora_dict_obj_name 操作对象名称 --ora_dict_obj_type 操作对象类型 --启用触发器 alter trigger schema_trigger enable; --禁用触发器 alter trigger schema_trigger disable;
-------------------------自己写的-------------------
-----------------------测试表---------------------
create table Test
(
iKey int,
iValue int,
primary key(iKey)
);
create table Test_Log
(
iKey int,
iClass varchar(4)
);
create trigger Test_Tri after insert on Test
for each row
begin
if :new.iValue>90 then
insert into Test_log values(:new.iKey,1);
end if;
end;
insert into Test values(12,120);
insert into Test values(18,60);
insert into Test values(10,90);