can you write commit in triggers?

Answers were Sorted based on User's Feedback



can you write commit in triggers?..

Answer / pradeep mishra

yes but with autonomous transaction

Is This Answer Correct ?    10 Yes 0 No

can you write commit in triggers?..

Answer / 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

Is This Answer Correct ?    6 Yes 0 No

can you write commit in triggers?..

Answer / selvam.p

No, In normal condition, commit or rollback inside the
trigger is not possible.

Is This Answer Correct ?    5 Yes 2 No

can you write commit in triggers?..

Answer / sudhir

by default Trigger doesnt allow a commit;

however we can create a stored procedure which will only do
commit.

create or replace procedure to_commit_trigger
begin
commit();

end to_commit_trigger


now execute this stored procedure to_commit_trigger inside a
trigger to perform a commit. However it will cause serious
performance issues.

Is This Answer Correct ?    0 Yes 3 No

Post New Answer

More Oracle General Interview Questions

how table is defined in plsql table? how can i select column from plsql table? can i use select * from plsql table type?

1 Answers  


What is a nested table and how is it different from a normal table?

0 Answers  


Explain the use of control file?

0 Answers  


What are the Characteristics of Data Files ?

3 Answers  


What is a subquery in oracle?

0 Answers  


What is the simplest tool to run commands on oracle servers?

0 Answers  


What is recovery manager(rman) backup in Oracle?

0 Answers   MCN Solutions,


How are the index updates?

1 Answers  


8. Display the client name and order date for all orders using the natural join keywords.

1 Answers   Wipro,


How to turn on or off recycle bin for the session?

0 Answers  


Difference between hot backup vs. Cold backup?

0 Answers  


what is the scenario where you take the database to NoArchivelog mode?

0 Answers  


Categories
  • Oracle General Interview Questions Oracle General (1808)
  • Oracle DBA (Database Administration) Interview Questions Oracle DBA (Database Administration) (261)
  • Oracle Call Interface (OCI) Interview Questions Oracle Call Interface (OCI) (10)
  • Oracle Architecture Interview Questions Oracle Architecture (90)
  • Oracle Security Interview Questions Oracle Security (38)
  • Oracle Forms Reports Interview Questions Oracle Forms Reports (510)
  • Oracle Data Integrator (ODI) Interview Questions Oracle Data Integrator (ODI) (120)
  • Oracle ETL Interview Questions Oracle ETL (15)
  • Oracle RAC Interview Questions Oracle RAC (93)
  • Oracle D2K Interview Questions Oracle D2K (72)
  • Oracle AllOther Interview Questions Oracle AllOther (241)