DISBALE/ENABLE A TRIGGER IN THE PROCEDURE

I have the procedure and what it does is to insert data into the table.

the question is how can I disable a trigger in the procedure. Could someone please give me the syntax of how to do this

thanks much

You can issue an execute immediate similar to the following:

sql_code := 'ALTER TRIGGER user.xyz DISABLE;';
execute immediate sql_code;

Regards

I'll have to put on my chrisrlong hat...

A trigger was implemented on that table for a specific reason. Either the trigger doesn't do what it should, or you are trying to circumvent the design. Either way, a trigger should not be disabled during a procedure. What if other users were doing an operation on the table that needed the trigger? What if your procedure failed and never re-enabled the trigger? Any way you look at it, it's a bad idea.

Jeff,

thanks for yoru advise but in this particular situation, the users want to disable the trigger.


Jovery,

Thanks for the advise but the code is not quite working yet.

when I declare SQL_CODE, can I declare it as


sql_code varchar2(100); ??????


B/c the code won't compile if you don't declare

Sorry I missed that line out, yes you should declare it.

Your suggestion is fine

Regards

for some reason I got


Error-911 ORA-00911:L invalid character

Please advise

Quote:

---

Originally posted by jovery
You can issue an execute immediate similar to the following:

sql_code := 'ALTER TRIGGER user.xyz DISABLE;';
execute immediate sql_code;

Regards

---

sql_code := 'ALTER TRIGGER user.xyz DISABLE';

Quote:

---

Originally posted by ashley75
Jeff,

thanks for yoru advise but in this particular situation, the users want to disable the trigger.

---

Thank God I'm not responsible for THAT database!