Answer Posted / santosh kumar

Yes ,You can Commit inside the trigger.

But for this you have to make this trigger transaction to be a Independent transaction from its parent transaction, You can do this by using Pragma. Pragma AUTONOMOUS_TRANSACTION allow you to build the Indepadent(child) Transaction,started by another. Shold be declare in DECLARE section of any subprogram.

Used to make Modular and Resuable Blocks. if you need the example then ask to me.

CREATE OR REPLACE TRIGGER TRIG_ARG
AFTER INSERT ON TAB1
DECLARE
PRAGMA AUTONOMOUNS_TRNASACTION
BEGIN
INSERT INTO LOG VALUES(SYSDATE,'INSERT ON TAB1');
COMMIT;
END;
/

OR

CREATE OR REPLACE TRIGGER t_trigger
AFTER INSERT ON t1 FOR EACH ROW

DECLARE
PRAGMA AUTONOMOUS_TRANSACTION;
i PLS_INTEGER;
BEGIN
SELECT COUNT(*)
INTO i
FROM t1;

INSERT INTO t2
VALUES
(i);
COMMIT;
END;
/

What is mutating sql table?

1112

---

How many types of literals are available in pl sql?

1119

---

What is sql profiling in oracle?

1064

---

What does partition by mean in sql?

962

---

What type of join is sql join?

982

---

Can a foreign key have a different name?

940

---

Explain the methods used to protect source code of pl/sql.

1019

---

what is the difference between clustered and non clustered index in sql? : Sql dba

1127

---

Is full outer join same as cross join?

970

---

What are the sql versions?

950

---

Is primary key always clustered index?

1023

---

what is bdb (berkeleydb)? : Sql dba

1034

---

What is percent sign in sql?

1283

---

How to call shell script from pl sql procedure?

1280

---

Which is better varchar or nvarchar?

997

---