Trigger issue - Oracle SQL plus

Question

I have created a trigger to insert values into Payroll once an entry to Employees have been made. But when I insert values into Employees , no values are entered into Payroll from the trigger. Payroll remains empty when I insert values into Employees. Any ideas?

CREATE TABLE Employees(
empid FLOAT,
dept FLOAT,
empname varchar2(25),
salary FLOAT
);

CREATE TABLE Payroll(
empid FLOAT,
salary FLOAT
);

CREATE OR REPLACE TRIGGER NewEmployee
AFTER INSERT 
ON Employees
FOR EACH ROW
BEGIN
INSERT INTO Payroll 
VALUES(:Old.empid,:Old.salary);
END;
/


INSERT INTO Employees values (1,1,‘supply’,50000);
INSERT INTO Employees values (2,2,‘hard’,80000);

Select * from Employees;
Select * from payroll;

Don't change your question that way! if you change it you invalidate the provided answers. Please, read the FAQ. If you have a new question, post it in a separate thread. I rolled back this to the first version. — jachguate
– jachguate, Commented Dec 7, 2012 at 2:47

jachguate · Accepted Answer · 2012-12-07 02:34:05Z

1

Your :Old.empid and :Old.salary should be :new.empid and :new.salary.

Also, it is slightly more efficient to use a BEFORE INSERT trigger when you can.

edited Dec 7, 2012 at 2:34

jachguate

17.2k3 gold badges62 silver badges101 bronze badges

answered Dec 7, 2012 at 1:58

EvilBob22

7425 silver badges12 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

jachguate Over a year ago

Please, include a link to support your statement: it is slightly more efficient to use a BEFORE INSERT trigger when you can. IMHO, inserting values in other tables is better done in AFTER triggers. If something fails during the insertion (check or foreign constraint for example) is less work to do to the engine to get the previous state before returning.

EvilBob22 Over a year ago

Good point on the failed insertion. I've heard somewhere that BEFOREs are more efficient, never really did any testing. I do have a link though: docs.oracle.com/cd/B19306_01/appdev.102/b14251/… "BEFORE row triggers are slightly more efficient than AFTER row triggers. With AFTER row triggers, affected data blocks must be read (logical read, not physical read) once for the trigger and then again for the triggering statement. Alternatively, with BEFORE row triggers, the data blocks must be read only once for both the triggering statement and the trigger."

Nidhin_toms Over a year ago

why should we use new instead of old?

jachguate Over a year ago

@Nidhin because you're inserting a new row. In before/after insert triggers OLD have no values. In before/after delete, NEW have no values. Both exists only in before/after update triggers.

jachguate Over a year ago

@Evil I edited your question to include the link. +1, I have missed that bit of code. Even with that, I think it makes more sense to have insert/update/delete other tables in the after triggers in the majority of the cases. One exception can be to do that for logging purposes. Unless you're under very high volume and performance is crucial (I bet the difference is in the order of nanoseconds per insert).

|

Collectives™ on Stack Overflow

Trigger issue - Oracle SQL plus

1 Answer 1

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related