can you write commit in triggers?

Question Posted / guest

4 Answers
10850 Views
I also Faced
E-Mail Answers

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