Trigger fo DML operations on view in SQL

Question

I'm doing a triggers which do DML operations on view. There are two sample tables:

Vehicle: vehicle_id, size, brand_id

Brand: brand_id, name

I created a view:

CREATE OR REPLACE VIEW vehicle_view AS
SELECT vehicle_id, size, brand.name FROM Vehicle
JOIN Brand USING (brand_id)

How can I do UPDATE trigger? I successfully did trigger for INSERT, but I can't find way to create UPDATE trigger.

CREATE OR REPLACE TRIGGER tr_vehicle_update
INSTEAD OF INSERT OR UPDATE ON vehicle_view
BEGIN
  UPDATE Vehicle SET
    vehicle_id = :new.vehicle_id
    WHERE size = :new.size; 
  UPDATE Brand SET
    brand_id = :new.brand_id
    WHERE name = :new.name; 
END;

Any error message you want to share with us? – sticky bit May 19 '18 at 00:29 — sticky bit, May 19 '18 at 00:29

score 1 · Accepted Answer · answered May 19 '18 at 07:27

BRAND_ID is the foreign key which joins the two tables. So you really, really don't want to change that. And VEHICLE_ID is the primary key of VEHICLE, so you don't want to change that either.

Probably your logic should be this:

CREATE OR REPLACE TRIGGER tr_vehicle_update
INSTEAD OF INSERT OR UPDATE ON vehicle_view
for each row
BEGIN
  if updating then
    if :new.size != :old.size then
       UPDATE Vehicle SET size = :new.size
       WHERE vehicle_id = :new.vehicle_id;
    end if;
    if  :new.name != :old.name then
       UPDATE Brand SET name = :new.name
       WHERE brand_id = :new.brand_id; 
    end if;
  end if;
  -- put your IF INSERTING logic here
END;

Note that INSTEAD OF triggers can only be FOR EACH ROW, so you need to specify that.

Trigger fo DML operations on view in SQL

1 Answers1