CODEWITHSUNDEEP
sqloracle-databaseoracle-sqldeveloper
captain n3mo
captain n3mo

Reputation: 5

How avoid row-level locking in PL/SQL

I have a table containing 3 columns eid,salary,newsal.

I have already inserted eid and salary for all the employees. Now,if I update the salary of any employee I want a trigger which directly increments the salary of that employee by 20% and stores it into the column newsal.

I used a trigger :

create or replace trigger pp1
after update on empsal
for each row
BEGIN
:new.newsal := :old.sal*1.2;
END;

I can't execute this trigger as it says

Error report: ORA-04084: cannot change NEW values for this trigger type 04084. 00000 - "cannot change NEW values for this trigger type" *Cause: New trigger variables can only be changed in before row insert or update triggers. *Action: Change the trigger type or remove the variable reference.

My table is defined as:

> create table empsal(empno varchar2(8),sal number,newsal number,primary
> key(empno));

Upvotes: 0

Views: 707

Answers (2)

Jacob
Jacob

Reputation: 14741

You can create a BEFORE UPDATE trigger as

CREATE OR REPLACE TRIGGER test_trg
   BEFORE UPDATE
   ON empsal
   FOR EACH ROW
BEGIN
   :NEW.newsal := :new.sal * 1.2;
END;

E.g.

INSERT INTO empsal (empno, sal)
     VALUES (123, 120);

enter image description here

UPDATE empsal
   SET sal = 130;


+-------+-----+--------+
| EMPNO | SAL | NEWSAL |
+-------+-----+--------+
|   123 | 130 |    156 |
+-------+-----+--------+

If you are using Oracle 11g, leverage the concept of virtual column, so try as 

CREATE TABLE empsal
(
   empno    VARCHAR2 (8),
   sal      NUMBER,
   newsal   GENERATED ALWAYS AS (sal * 1.2) VIRTUAL,
   PRIMARY KEY (empno)
);

Upvotes: 1

Stephan Lechner
Stephan Lechner

Reputation: 35154

Its because AFTER-triggers cannot write to the :new-values. Use a BEFORE-trigger instead. Confer, for example, this documentation on triggers (emphasis mine):

BEFORE

Specify BEFORE to cause the database to fire the trigger before executing the triggering event. For row triggers, the trigger is fired before each affected row is changed.

Restrictions on BEFORE Triggers BEFORE triggers are subject to the following restrictions:

  • You cannot specify a BEFORE trigger on a view or an object view.
  • You can write to the :NEW value but not to the :OLD value.

AFTER

Specify AFTER to cause the database to fire the trigger after executing the triggering event. For row triggers, the trigger is fired after each affected row is changed.

Restrictions on AFTER Triggers AFTER triggers are subject to the following restrictions:

  • You cannot specify an AFTER trigger on a view or an object view.
  • You cannot write either the :OLD or the :NEW value.

Upvotes: 1

Related Questions