can you write commit in triggers?
Answers were Sorted based on User's Feedback
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 |
Answer / selvam.p
No, In normal condition, commit or rollback inside the
trigger is not possible.
Is This Answer Correct ? | 5 Yes | 2 No |
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 |
please explain.. DB architecture ...
How to display First 4 rows From Database Table?
Explain table?
How to rename an index in oracle?
What is the difference between column level constraints and table level constraints?
14 Answers IBM, TCS,
WHAT IS ecc 6.0
What happens if the imported table already exists?
What is index in Oracle?
What do the 9i dbms_standard.sql_txt() and dbms_standard.sql_text() procedures do?
what is the need of indexing topic in oracle? where do we use in a Java project? any other option other than using this to get the same result where we use indexing ?
How to view existing locks on the database?
how to find full database size
1 Answers BirlaSoft, Consultancy,