CODEWITHSUNDEEP
sqltriggersdb2
Landers
Landers

Reputation: 89

How do I create a Trigger on a View that Updates a Column in a separate table?

I am wondering if it's possible to create a trigger on a view after insert that updates a column in another table? Below is an example of what I wrote.

View Name: VMC_FWD2

Table Name: TLORDER

CREATE OR REPLACE TRIGGER QA_TRACE_NUM_INSERT_OI
AFTER INSERT ON VMC_FWD2
REFERENCING NEW ROW AS N
FOR EACH ROW
MODE DB2SQL 
BEGIN ATOMIC 
update tlorder set user4 = n.status where bill_number = n.number;
END

However this produces an error:

[IBM][CLI Driver][DB2/NT64] SQL0156N The name used for this operation is not a table. LINE NUMBER=2. SQLSTATE=42809"

and a little more research says you can't use standard triggers on views and that there is a workaround, just not what it is.

Upvotes: 0

Views: 2208

Answers (1)

mustaccio
mustaccio

Reputation: 19001

Views support only INSTEAD OF triggers, so yours would look like 

CREATE OR REPLACE TRIGGER QA_TRACE_NUM_INSERT_OI
INSTEAD OF INSERT ON VMC_FWD2
...  -- skipped other declaration lines
BEGIN ATOMIC 
  update tlorder set user4 = n.status where bill_number = n.number;
END

Upvotes: 1

Related Questions