Update column value in trigger

View All Scripts Login to Run Script

Script Name Update column value in trigger
Description An answer to a twitter request: Many thanks Steven for your reply ... As u mentioned I am asking little different... I would like to update emp_id column before insert in employee table using some validation and in that validation I am passing :new.emp_id value and if my validation satisfy update the emp_id column to the new value and then insert into employee table ... Begin Cursor ( passing emp_id) Validation If validation satisfy Fetching cursor value to variable Then :NEW.emp_id=variable But it seems we can't update the same column which we are passing and using old values using before insert trigger ... Plz advice me regarding this
Area PL/SQL General / PL/SQL Triggers
Contributor Steven Feuerstein
Created Friday October 07, 2016

Statement 1

CREATE TABLE t
(
   n   NUMBER,
   d   DATE
)

Table created.

Statement 2

CREATE PROCEDURE p (n_io IN OUT NUMBER)
IS
BEGIN
   IF n_io > 100
   THEN
      n_io := 50;
   END IF;
END;

Procedure created.

Statement 3

CREATE OR REPLACE TRIGGER tr 
   BEFORE UPDATE OR INSERT 
   ON t 
   FOR EACH ROW 
BEGIN 
   p (:new.n); 
END;

Trigger created.

Statement 4

INSERT INTO t
     VALUES (150, SYSDATE)

1 row(s) inserted.

Statement 5

INSERT INTO t 
     VALUES (75, sysdate + 10)

1 row(s) inserted.

Statement 6

SELECT * FROM t


N D
50 07-OCT-16
75 17-OCT-16

2 rows selected.

N	D
50	07-OCT-16
75	17-OCT-16

Additional Information