| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: create assertion?
"eugene kim" <eugene1977_at_hotmail.com> wrote in message
news:aldhi1$v6$1_at_newsreader.mailgate.org...
> about triggers,
> if user insert a row into orders table,
> i want a trigger to set duedate attribute of that row to sysdate+10
>
>
> create trigger s_duedate after insert on orders
> begin
> update orders set duedate = sysdate+10;
> end s_duedate;
> /
>
> as you pointed out, it will update all the rows in column..
>
> i looked at application developer's guide.
> i tried
> using for each row + :old (:old represents the table before insertion? in
my
> case?... it's just not clear, i spent good deal of time figuring out 'each
> row' just indicates trigger get fired for each affected row, (so i don't
> have any info about the specific row inside for each statement)
> referencing new row as newr ..
>
>
> create trigger s_duedate after insert on orders
> for each row
> begin
> update orders set duedate = sysdate+10
> where :old.order_id = order_id;
> end s_duedate;
> /
> ERROR at line 1:
> ORA-04091: table CSI4335.ORDERS is mutating, trigger/function may not see
it
> ORA-06512: at "CSI4335.S_DUEDATE", line 2
> ORA-04088: error during execution of trigger 'CSI4335.S_DUEDATE'
I'm not sure if you are trying to learn about triggers or code appropriately so I'll offer 2 alternatives based on the following 'need'
'I need oracle to set the value of the duedate column to 10 days after the date at which the row was created, when the row is created'.
This can be accomplished in 2 ways. The first is via a trigger as you are trying. the variable :new references the new values of the row which is being affected. The easiest way to update the duedate column is then to set the value of the duedate field in the :new variable to the desired value. you would do this like this.
create trigger s_duedate before insert on orders
for each row
begin
:new.duedate = sysdate+10;
end s_duedate;
/
This has the advantage of fulfilling the need I expressed above, but fails somewhat dismally if I decide that for a particular order i'll be really generous and set the due date 15 days from now. I'd then need an insert and an update. Now I *could* get all clever and decide to test for the existence of a duedate in the before insert trigger, but to be honest I couldn't be bothered so i'd use the second approach. I'd use default values.
To do this i would create my table as follows
create table orders(
order_id number primary key,
customer_id number,
description varchar2(255),
duedate date default sysdate + 10)
tablespace <whereever i decide to put it>;
Then whenever i inserted into the orders table each row would get a value of ten days into the future for the duedate column *unless I decided to specify otherwise*).
eg (NB I put the typo's down to it being Saturday. it's nothing to do with the beer honest ).
SQL> create table orders(
2 order_id number primary key,
3 customer_id number,
4 description varchar2(255),
5 duedate date default sysdate + 10)
6 tablespace <whereever i decide to put it>;
tablespace <whereever i decide to put it>
*
ERROR at line 6:
ORA-02216: tablespace name expected
SQL> ed
Wrote file afiedt.buf
1 create table orders(
2 order_id number primary key,
3 customer_id number,
4 description varchar2(255),
5 duedate date default sysdate + 10)
6* tablespace users
SQL> /
Table created.
SQL> insert into orders(order_id,cuistomer_id,description)
2 .
SQL> insert into orders(order_id,customer_id,description)
2 values(1,1,'David Bowie: Greatest Hits);
ERROR:
ORA-01756: quoted string not properly terminated
SQL> ed
Wrote file afiedt.buf
1 insert into orders(order_id,customer_id,description) 2* values(1,1,'David Bowie: Greatest Hits') SQL> / 1 row created.
SQL> insert into orders(order_id,customer_id,description)
2 values(1,1,'Pink Floyd:Dark Side of the Moon');
insert into orders(order_id,customer_id,description)
*
ERROR at line 1:
ORA-00001: unique constraint (NIALL.SYS_C002195) violated
SQL> ed
Wrote file afiedt.buf
1 insert into orders(order_id,customer_id,description) 2* values(2,1,'Pink Floyd:Dark Side of the Moon') SQL> / 1 row created.
SQL> insert into orders(order_id,customer_id,description,duedate) 2* values(3,1,'Madonna:Like A Virgin',to_date('12/10/2002','dd/mm/yyyy')) SQL> / 1 row created.
SQL> commit;
Commit complete.
SQL> select * from orders;
ORDER_ID CUSTOMER_ID
---------- -----------
DESCRIPTION
1 1
David Bowie: Greatest Hits
17-SEP-02
2 1
Pink Floyd:Dark Side of the Moon
17-SEP-02
ORDER_ID CUSTOMER_ID
---------- -----------
DESCRIPTION
3 1
Madonna:Like A Virgin
12-OCT-02
-- Niall Litchfield Oracle DBA Audit Commission UK ***************************************** Please include version and platform and SQL where applicable It makes life easier and increases the likelihood of a good answer ******************************************Received on Sat Sep 07 2002 - 14:28:12 CDT
 |
 |