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 |
25. Display the client number and the value of the highest value order placed by that client.
query to find the maximum no persons with same age(age colomn) from emp table
Use of an integrity constraint is better to validate data. Explain
When do we use group by clause in a sql query?
What is recycle bin in Oracle?
What exactly do quotation marks around the table name do?
State the difference along with examples between Oracle 9i, Oracle 10g and Oracle 11i.
SQL> CREATE TABLE to_table 2 (col1 NUMBER); Table created. SQL> CREATE OR REPLACE TRIGGER statement_trigger 2 AFTER INSERT ON to_table 3 BEGIN 4 DBMS_OUTPUT.PUT_LINE('After Insert Statement Level'); 5 END; 6 / Trigger created. SQL> CREATE OR REPLACE TRIGGER row_trigger 2 AFTER INSERT ON to_table 3 FOR EACH ROW 4 BEGIN 5 DBMS_OUTPUT.PUT_LINE('After Insert Row Level'); 6 END; 7 / Trigger created. SQL> INSERT INTO TO_TABLE VALUES(1); After Insert Row Level After Insert Statement Level 1 row created. SQL> BEGIN 2 INSERT INTO TO_TABLE VALUES(2); 3 INSERT INTO TO_TABLE VALUES(3); 4 INSERT INTO TO_TABLE VALUES(4); 5 INSERT INTO TO_TABLE VALUES(5); 6 INSERT INTO TO_TABLE VALUES(6); 7 INSERT INTO TO_TABLE VALUES(7); 8 INSERT INTO TO_TABLE VALUES(8); 9 INSERT INTO TO_TABLE VALUES(9); 10 INSERT INTO TO_TABLE VALUES(0); 11 END; 12 / WAT LL BE THE O/P??? XPLAIN IT>>>>
What is a public synonym?
 How to use an oracle sequence generator in a mapping?
Explain the difference between replace() and translate() functions in oracle?
How to open a cursor variable?