create trigger test before update on table1 referencing old as old_value for each row begin if updating then update table1 set prerank =:old_value.prerank; end if; end;
-- Create table create table ALL_REPLIES ( TITLE VARCHAR2(2000), STATUS VARCHAR2(10), RESULT_MY VARCHAR2(100) ); -- Add comments to the columns comment on column ALL_REPLIES.TITLE is '帖子名称'; comment on column ALL_REPLIES.STATUS is '得分状态 1为得分 0为未得分'; comment on column ALL_REPLIES.RESULT_MY is '回帖人感受';--trigger : CREATE OR REPLACE TRIGGER BF_INT_UPD_ALL_REPLIES BEFORE INSERT OR UPDATE ON ALL_REPLIES FOR EACH ROW BEGIN IF :NEW.STATUS = '1' THEN :NEW.RESULT_MY := 'HAPPY' ; ELSIF :NEW.STATUS = '0' THEN :NEW.RESULT_MY := 'NOT HAPPY' ; ELSE :NEW.RESULT_MY := 'Garbage data' ; END IF ; END BF_INT_UPD_ALL_REPLIES ; /INSERT INTO ALL_REPLIES(TITLE,STATUS) VALUES ('PL/SQL里面怎么写一张表的触发器,求例子','0') ;COMMIT ; select * from all_replies; 1 PL/SQL里面怎么写一张表的触发器,求例子 0 NOT HAPPYupdate all_replies set status = '1' where title='PL/SQL里面怎么写一张表的触发器,求例子' ;select * from all_replies;
create trigger test before update on table1 referencing old as old_value for each row begin if updating then if :new.prerank > 0 then :new.prerank := -1; end if; end if; end;
SQL> Create Or Replace Trigger tri_status_update 2 After insert or update of object_id on b11 3 for each row 4 Begin 5 if (:new.object_id= 9) then 6 Insert into test_tri 7 (sj) 8 Values 9 (sysdate); 10 end if; 11 End; 12 /Trigger createdSQL> update b11 set object_id=9 where object_id = 51;6 rows updatedSQL> commit;Commit completeSQL> select * from test_tri;SJ ----------- 2011-7-2 14 2011-7-2 14 2011-7-2 14 2011-7-2 14 2011-7-2 14 2011-7-2 146 rows selectedSQL>
和这个差不多 execute immediate
for each row
begin
if updating then
update table1 set prerank =:old_value.prerank;
end if;
end;
-- Create table
create table ALL_REPLIES
(
TITLE VARCHAR2(2000),
STATUS VARCHAR2(10),
RESULT_MY VARCHAR2(100)
);
-- Add comments to the columns
comment on column ALL_REPLIES.TITLE
is '帖子名称';
comment on column ALL_REPLIES.STATUS
is '得分状态 1为得分 0为未得分';
comment on column ALL_REPLIES.RESULT_MY
is '回帖人感受';--trigger :
CREATE OR REPLACE TRIGGER BF_INT_UPD_ALL_REPLIES
BEFORE INSERT OR UPDATE ON ALL_REPLIES
FOR EACH ROW
BEGIN
IF :NEW.STATUS = '1' THEN
:NEW.RESULT_MY := 'HAPPY' ;
ELSIF :NEW.STATUS = '0' THEN
:NEW.RESULT_MY := 'NOT HAPPY' ;
ELSE
:NEW.RESULT_MY := 'Garbage data' ;
END IF ;
END BF_INT_UPD_ALL_REPLIES ;
/INSERT INTO ALL_REPLIES(TITLE,STATUS) VALUES ('PL/SQL里面怎么写一张表的触发器,求例子','0') ;COMMIT ;
select * from all_replies;
1 PL/SQL里面怎么写一张表的触发器,求例子 0 NOT HAPPYupdate all_replies set status = '1' where title='PL/SQL里面怎么写一张表的触发器,求例子' ;select * from all_replies;
create trigger test before update on table1 referencing old as old_value
for each row
begin
if updating then
if :new.prerank > 0 then
:new.prerank := -1;
end if;
end if;
end;
2 After insert or update of object_id on b11
3 for each row
4 Begin
5 if (:new.object_id= 9) then
6 Insert into test_tri
7 (sj)
8 Values
9 (sysdate);
10 end if;
11 End;
12 /Trigger createdSQL> update b11 set object_id=9 where object_id = 51;6 rows updatedSQL> commit;Commit completeSQL> select * from test_tri;SJ
-----------
2011-7-2 14
2011-7-2 14
2011-7-2 14
2011-7-2 14
2011-7-2 14
2011-7-2 146 rows selectedSQL>