CODEWITHSUNDEEP
sqloracle-databasetriggers
Hoodie
Hoodie

Reputation: 73

Oracle trigger change value of column/row from one table during insert of another table

I have a column in a customer table that states whether a customer is active or an inactive on my customer table (cus_status). I have a table that tracks the jobs/order by customers. I want the cus_status column for the inactive customer to change to active when that same customer puts in an order from my job table.

So if billy who is listed as 'inactive' in the customer table puts in an order and now has an entry in my job table, then I want the trigger to adjust that column to 'active'.

Is this possible? And if so, how would I achieve this?

Upvotes: 0

Views: 34

Answers (1)

Popeye
Popeye

Reputation: 35900

Use after insert trigger as following (see inline comments for more detail):

CREATE TRIGGER trg_name   -- give the trigger a name
AFTER INSERT ON job       -- job is the name of the table
FOR EACH ROW              -- trigger will fire for each insert, row level trigger
AS
BEGIN
    UPDATE customer
    SET cust_status = 'ACTIVE'
    WHERE CUST_ID = :NEW.CUST_FK -- CUST_FK is column of job table referring to customer table
      AND cust_status = 'INACTIVE';
END;
/

Cheers!!

Upvotes: 1

Related Questions