CODEWITHSUNDEEP
sqloracle-databaseplsql
tic30
tic30

Reputation: 49

SQL procedure called from trigger

In Oracle environment (SQLPLUS I used). My question is how to alter the data of a newly inserted tuple. Here is an example: I need to make sure whenever an insert happen to table "Orders", if its attribute "weight" is bigger than 100, its another attribute "size_level" must be 1. Otherwise (weight<=100), size_level must be 0. In order to do this, I figured that I need to call a stored procedure.

CREATE OR REPLACE TRIGGER new_ship_trigger
AFTER INSERT ON Orders
FOR EACH ROW
BEGIN ATOMIC
  CALL UpdateShipSizeLevel(:new)
END;
/

How should I write the code for the stored procedure part? Or maybe I need a cursor? I am required NOT to add a constraint to table "Orders" and I should use no more than one trigger.

Upvotes: 0

Views: 109

Answers (1)

user272735
user272735

Reputation: 10648

Here you'll find yet another Oracle trigger example. Also be sure to read e.g. PL/SQL Triggers.

create table so54b (
 id number
,weight number
,weight_level number
);

create or replace trigger so54b_trg
-- note the trigger is also run in update
before insert or update on so54b
for each row
begin
  -- you don't need to implement the trigger logic in a separate
  -- subroutine. however sometimes it might make sense.
  :new.weight_level :=
    case
      when :new.weight > 100 then 1
      else 0
    end;
end;
/
show errors

insert into so54b(id, weight) values (1, 99);
insert into so54b(id, weight) values (2, 100);
-- weight_level is overwritten by the trigger
insert into so54b(id, weight, weight_level) values (3, 101, 13);

select * from so54b order by id;

        ID     WEIGHT WEIGHT_LEVEL
---------- ---------- ------------
         1        99             0
         2       100             0
         3       101             1


update so54b set weight = 80 where weight > 100;

select * from so54b order by id;

        ID     WEIGHT WEIGHT_LEVEL
---------- ---------- ------------
         1         99            0
         2        100            0
         3         80            0

Upvotes: 1

Related Questions