CODEWITHSUNDEEP
oracle-databaseplsqltriggersdateadd
user3071845
user3071845

Reputation: 47

Trigger for incrementing a date and inserting into another table

I want to create a trigger, in Oracle. When the dateOrdReceived in my order table is updated or inserted the trigger takes this date whatever it may be and updates it by 14 days into another table productList ordDateDelivery so that it equals to

dateOrdReceived + 14 days = new ordDateDelivery

I did have a couple of attempts and guessed I'd need a query which would join my two tables. I also learned that maybe using DATEADD would allow me add 14 days but altogether I can't quite get it right.

My trigger attempt

`CREATE OR REPLACE TRIGGER  "PRODUCTLIST_DATE_DELIVERY" 
BEFORE
insert or update on "PRODUCTLIST"
for each row

begin
select p.dateOrdRecieved, o.ordDateDelivery
from productList p JOIN orders o
ON p.ordID = o.ordID;

new.OrdDateDelivery := DATEADD(day,14,new.p.dateOrdRecieved)

end;


/
ALTER TRIGGER  "PRODUCTLIST_DELIVERY_DATE" ENABLE

and my tables for this trigger are as follows

PRODUCTLIST TABLE

CREATE TABLE  "PRODUCTLIST" 
(   "ORDID" NUMBER(3,0) NOT NULL ENABLE, 
"PRODUCTID" NUMBER(3,0) NOT NULL ENABLE, 
"QUANTITY" NUMBER(4,2) NOT NULL ENABLE, 
"ORDDATEDELIVERY" DATE, 
"DISCOUNT" NUMBER(3,0), 
"TOTALCOST" NUMBER(4,2), 
 CONSTRAINT "PK_PRODUCTLIST" PRIMARY KEY ("ORDID", "PRODUCTID") ENABLE
)
/
ALTER TABLE  "PRODUCTLIST" ADD CONSTRAINT "FK_ORDERS" FOREIGN KEY ("ORDID")
  REFERENCES  "ORDERS" ("ORDID") ENABLE
/
ALTER TABLE  "PRODUCTLIST" ADD CONSTRAINT "FK_PRODUCTS" FOREIGN KEY ("PRODUCTID")
  REFERENCES  "PRODUCT" ("PRODUCTID") ENABLE
/

ORDERS TABLE

CREATE TABLE  "ORDERS" 
(   "ORDID" NUMBER(3,0) NOT NULL ENABLE, 
"DATEORDRECIEVED" DATE, 
"CUSID" NUMBER(3,0) NOT NULL ENABLE, 
 PRIMARY KEY ("ORDID") ENABLE
)
/
ALTER TABLE  "ORDERS" ADD CONSTRAINT "FK_CUSTOMER" FOREIGN KEY ("CUSID")
  REFERENCES  "CUSTOMER" ("CUSID") ENABLE
/

Upvotes: 0

Views: 1549

Answers (1)

Ben
Ben

Reputation: 52863

DATEADD() is not an Oracle function... Oracle's datetime arithmetic is based around the day. If you add 1 to a date it increments the date by one day, adding 1.5 by 36 hours etc.

Now, your trigger.

You can't automatically update or insert a record into another table. The trigger is "on" one table, which means you need to create the DML in order to add or update it into that table.

update productlist
   set dateOrdRecieved = :new.OrdDateDelivery + 14
 where ordid = :new.ordid

The :new. here references the new data of the table on which the trigger is on. It's a specific "variable" that you can access rather than a general concept of what you're trying to achieve. You can't use it to assign data to other tables directly, though you can use it as a means of doing so.

Next you need to consider where your trigger is. You're looking to update PRODUCTLIST whenever ORDERS is changed, this means that the trigger needs to be on the table ORDERS.

create or replace trigger productlist_date_delivery
 before insert or update on orders
 for each row
begin
    update productlist
       set OrdDateDelivery = :new.dateOrdRecieved + 14
     where ordid = :new.ordid;
end;
/

Notice a few extra differences to your own:

  1. I use :new. instead of new.
  2. I'm not selecting from the table; there's no need to do this as the data is already available. It's also impossible as you're selecting data that Oracle's trying to update, it forbids this to ensure integrity.
  3. I haven't used cased identifiers. There's no need to do this; Oracle upper-cases everything by default. It's also really painful if everything's not upper case as you have to remember
  4. Every statement ends in a semi-colon.

If you're having problems I recommend Tech on the Net, it has a good basic guide. As always though, there's the documentation on the CREATE TRIGGER statement.

Upvotes: 3

Related Questions