要在更新属性上执行的TRIGGER？
I have a problem with triggers and I can't find why.
我有触发器的问题,我找不到原因。
These are the tables:
这些是表格:
create table Properties(
idProperties number(10) NOT NULL ,
Type varchar2(45) NOT NULL,
ConstructionDate date NOT NULL,
FloorLocation varchar(20),
Balkony varchar2(10),
Price number(10) NOT NULL,
DateOfInsert date NOT NULL,
DateOfExiration date NOT NULL,
Address_FK number(20),
PropertyType_FK number(20) NOT NULL,
Service_FK number(20),
Ownership_FK number(20),
PropertyService_FK number(20))
create table Services(
idServices number(10) NOT NULL,
servicetype varchar2(20))
I am trying write a trigger to be executed on update of a PROPERTY when the status field is changed from “NEW” to “APROVED” then update expiration date with today’s date (Sysdate) + 90 days.
当状态字段从“NEW”更改为“APROVED”然后用今天的日期(Sysdate)+ 90天更新到期日期时,我正在尝试编写一个触发器,以便在更新PROPERTY时执行。
I am trying this but it doesn't work:
我正在尝试这个但它不起作用:
CREATE OR REPLACE TRIGGER T22
AFTER UPDATE ON Properties
FOR EACH ROW
Begin
if :new.servicetype = 'APROVED' then
:new.Servicetype := SYSDATE + 90;
end if;
End;
Do I need to call servicetype with foreign key from the properties table? Or is this right but I am missing something?
我是否需要从属性表中使用外键调用servicetype?或者这是对的,但我错过了什么?
2 个解决方案
#1
2
You cannot change a value in an AFTER trigger. You would need to use a BEFORE trigger.
您无法更改AFTER触发器中的值。您需要使用BEFORE触发器。
If you're using Oracle 11.2, you should get an "ORA-04084: cannot change NEW values for this trigger type" error if you try to define an AFTER trigger that changes a value
如果您使用的是Oracle 11.2,如果您尝试定义更改值的AFTER触发器,则应该得到“ORA-04084:无法更改此触发器类型的新值”错误
SQL> create table t (
2 col1 number,
3 col2 date
4 );
Table created.
SQL> create trigger trg_t
2 after update on t
3 for each row
4 begin
5 :new.col2 := sysdate + 90;
6 end;
7 /
create trigger trg_t
*
ERROR at line 1:
ORA-04084: cannot change NEW values for this trigger type
On the other hand, if you create the trigger as a BEFORE UPDATE trigger, it should work
另一方面,如果您将触发器创建为BEFORE UPDATE触发器,它应该可以工作
SQL> ed
Wrote file afiedt.buf
1 create or replace trigger trg_t
2 before update on t
3 for each row
4 begin
5 :new.col2 := sysdate + 90;
6* end;
SQL> /
Trigger created.