Answer Posted / guru

----------- without AUTONOMOUS_TRANSACTION

CREATE or REPLACE TRIGGER parts_trig
BEFORE INSERT ON parts
FOR EACH ROW
BEGIN
INSERT INTO parts_log VALUES(:new.pnum, :new.pname);
END;
/
INSERT INTO parts VALUES (1040, 'Head Gasket');
COMMIT;
INSERT INTO parts VALUES (2075, 'Oil Pan');
ROLLBACK;

SELECT * FROM parts ORDER BY pnum;
SELECT * FROM parts_log ORDER BY pnum;

----------- using AUTONOMOUS_TRANSACTION

CREATE or REPLACE TRIGGER parts_trig
BEFORE INSERT ON parts FOR EACH ROW
DECLARE
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
INSERT INTO parts_log VALUES(:new.pnum, :new.pname);
COMMIT; -- only for AUTONOMOUS_TRANSACTION
END;
/
INSERT INTO parts VALUES (1040, 'Head Gasket');
COMMIT;
INSERT INTO parts VALUES (2075, 'Oil Pan');
ROLLBACK;
/


Please Check This


Thanks

why should i declare foreign key constraint as self relation instead of binary relation in tables ?

1855

---

What is the relation of a user account and a schema?

913

---

What is the purpose of save points in oracle database?

773

---

What are the differences between interval year to month and interval day to second?

810

---

How to write a query with a right outer join in oracle?

903

---

Is oracle an open source?

845

---

Why do we need oracle client?

723

---

What is the difference between $oracle_base and $oracle_home?

846

---

What is a tns file?

756

---

What is control file used for?

844

---

What is the recommended interval at which to run statspack snapshots, and why?

2694

---

How to create a new view in oracle?

866

---

What are the uses of linked server and explain it in detail?

851

---

What happens to the current transaction if a ddl statement is executed?

796

---

How to create a new table by selecting rows from another table?

873

---