Home » SQL & PL/SQL » SQL & PL/SQL » Calling a Trigger from a Procedure.  () 2 Votes
Calling a Trigger from a Procedure. [message #156260] Tue, 24 January 2006 03:22 Go to next message
nirmalnarayan
Messages: 261
Registered: April 2005
Location: India
Senior Member
This question was asked in a recent interview.

'Can we call a trigger inside a Procedure?'

I gave the answer that there is no need to call a trigger inside a proc. as the trig. get executed automatically on any of DML statements, but the interviewer was not satisfied. He again repeated the same question, so i said 'NO'

Here is the script
----------------------------------


SQL> create or replace trigger ftrig
2 after insert on emp
3 begin
4 dbms_output.put_line('Trigger ');
5 exception when others then
6 null;
7 end;
8 /

Trigger created.

SQL> create or replace procedure calproc is
2 begin
3 execute immediate 'call ftrig()';
4 exception when others then
5 null;
6 end;
7 /

Procedure created.

SQL> call calproc();

Call completed.

SQL> call ftrig();
call ftrig()
*
ERROR at line 1:
ORA-06576: not a valid function or procedure name

-----------------------------------------------

We can call trigger using EXECUTE IMMEDIATE, but still i don't understand the stupid question from the stupid interviewer, why should one need to call a trigger inside a procedure?


Re: Calling a Trigger from a Procedure. [message #156262 is a reply to message #156260] Tue, 24 January 2006 03:53 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
If it was a Forms interview, the answer would have been 'yes'. A trigger is a named piece of PL/SQL code that is fired (triggered) by, like you said, a given event.

If it was a general SQL or PL/SQL interview I would have said 'no'.
On the database you have, apart from DML triggers, also DDL triggers. But even by using an EXECUTE IMMEDIATE you cannot call a trigger on the database explicitely. For example, a ROW trigger often uses :NEW and :OLD values, and those make no sense outside a DML statement.

But in Forms: yes. You'd use the EXECUTE_TRIGGER built-in. Could you have mistaken Forms triggers for database triggers?

MHE
Re: Calling a Trigger from a Procedure. [message #156267 is a reply to message #156262] Tue, 24 January 2006 04:50 Go to previous messageGo to next message
nirmalnarayan
Messages: 261
Registered: April 2005
Location: India
Senior Member
It was a 'pucca' PL/SQL Interview, what do you say ?

Rgds,

Nirmal
Re: Calling a Trigger from a Procedure. [message #156276 is a reply to message #156267] Tue, 24 January 2006 06:06 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Quote:

3 execute immediate 'call ftrig()';

This is not valid pl/sql anyway. Call is a sqlplus keyword.

Re: Calling a Trigger from a Procedure. [message #156360 is a reply to message #156276] Tue, 24 January 2006 23:26 Go to previous messageGo to next message
nirmalnarayan
Messages: 261
Registered: April 2005
Location: India
Senior Member
Frank wrote on Tue, 24 January 2006 07:06

Quote:

3 execute immediate 'call ftrig()';

This is not valid pl/sql anyway. Call is a sqlplus keyword.





Then why it did'nt give me any error ?

Anyway my question is not that, Why should one want to call a trigger inside a Procedure in PL/SQL ?

Rgds,

Nirmal Narayanan.
Re: Calling a Trigger from a Procedure. [message #156367 is a reply to message #156360] Wed, 25 January 2006 00:49 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
nirmalnarayan wrote on Wed, 25 January 2006 06:26


Then why it did'nt give me any error ?



Quote:

4 exception when others then
5 null;
6 end;


Because you told it not to give an error.

As Maaher said, calling a trigger only makes in forms.
Re: Calling a Trigger from a Procedure. [message #156369 is a reply to message #156276] Wed, 25 January 2006 01:11 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Frank wrote on Tue, 24 January 2006 23:06

Call is a sqlplus keyword.




Correction, EXEC is a SQLPLUS keyword. CALL is a SQL command. The giveaway is that you have to terminate a CALL with a semi-colon or slash to run it in SQL*Plus.

_____________
Ross Leishman
Re: Calling a Trigger from a Procedure. [message #156398 is a reply to message #156369] Wed, 25 January 2006 03:07 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Ross, you are right.
I was wrong in thinking (without testing) that call could not be used in execute immediate.
Another reason to test first, before stating 'the obvious'...

Thanks.
Re: Calling a Trigger from a Procedure. [message #156404 is a reply to message #156398] Wed, 25 January 2006 03:33 Go to previous messageGo to next message
nirmalnarayan
Messages: 261
Registered: April 2005
Location: India
Senior Member
Hi,

Anyway my question is not that, Why should one want to call a trigger inside a Procedure in PL/SQL ?

Then Why do they say "PL-SQL is in Sync with SQL" from Oracle 9i onwards?

Rgds,

Nirmal Narayanan

[Updated on: Wed, 25 January 2006 03:47]

Report message to a moderator

Re: Calling a Trigger from a Procedure. [message #156408 is a reply to message #156276] Wed, 25 January 2006 03:58 Go to previous messageGo to next message
nirmalnarayan
Messages: 261
Registered: April 2005
Location: India
Senior Member
Frank wrote on Tue, 24 January 2006 07:06

Quote:

3 execute immediate 'call ftrig()';

This is not valid pl/sql anyway. Call is a sqlplus keyword.





I think you are wrong 'Call can be used inside Execute Immediate '

Here is the script


SQL> create or replace procedure calproc is
2 begin
3 dbms_output.put_line('INSIDE PROCEDURE CALPROC');
4 end;
5 /

Procedure created.

SQL> create or replace procedure callfromproc is
2 begin
3 execute immediate 'call calproc()';
4 end;
5 /

Procedure created.

SQL> call callfromproc();
INSIDE PROCEDURE CALPROC

Call completed.

Re: Calling a Trigger from a Procedure. [message #156418 is a reply to message #156404] Wed, 25 January 2006 04:18 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
nirmalnarayan wrote on Wed, 25 January 2006 10:33

Hi,

Anyway my question is not that, Why should one want to call a trigger inside a Procedure in PL/SQL ?
One can want so many things but somethings are just not possible, like calling a DML trigger manually without performing the triggering event (i.e. the DML).

nirmalnarayan wrote on Wed, 25 January 2006 10:33

Then Why do they say "PL-SQL is in Sync with SQL" from Oracle 9i onwards?
Because in elder versions of Oracle, some advanced SQL features weren't available in PL/SQL. I don't see the relevance to your question. DECODE can also only be used in SQL. For CALL, the same restrictions apply.

Now, I have tested it. Using EXECUTE IMMEDIATE, you can call a procedure or function, but not a trigger.

Using this script
SET SERVEROUT ON

CREATE PROCEDURE PRC$FOO
IS
BEGIN
  DBMS_OUTPUT.PUT_LINE('Procedure foo called');
END;
/

CREATE TABLE mhe_foo(id NUMBER)
/

CREATE TRIGGER trg_mhe_foo
BEFORE INSERT ON mhe_foo
BEGIN
  dbms_output.put_line('Trigger on mhe_foo called.');
END;
/


BEGIN
  EXECUTE IMMEDIATE 'CALL prc$foo()'; -- procedure call
END;
/

BEGIN
  EXECUTE IMMEDIATE 'CALL trg_mhe_foo()'; -- trigger call, will fail.
END;
/


DROP TABLE mhe_foo
/


DROP PROCEDURE PRC$FOO
/
I got this result:
SQL> select banner from v$version
  2  /

BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.5.0 - 64bit Production
PL/SQL Release 9.2.0.5.0 - Production
CORE    9.2.0.6.0       Production
TNS for IBM/AIX RISC System/6000: Version 9.2.0.5.0 - Production
NLSRTL Version 9.2.0.5.0 - Production

SQL> @C:\useful\orafaq

Procedure created.


Table created.


Trigger created.

Procedure foo called

PL/SQL procedure successfully completed.

BEGIN
*
ERROR at line 1:
ORA-06576: not a valid function or procedure name
ORA-06512: at line 2



Table dropped.


Procedure dropped.
The ORA message is clear: it has to be a valid function or procedure.

MHE
Re: Calling a Trigger from a Procedure. [message #156465 is a reply to message #156418] Wed, 25 January 2006 05:20 Go to previous messageGo to next message
nirmalnarayan
Messages: 261
Registered: April 2005
Location: India
Senior Member
I agree with you that Trigger cannot be called from a Procedure (IN PL/SQL, NOT IN FORMS), but Procedures and Functions can and 'CALL' can be used with EXECUTE IMMEDIATE inside a procedure.

This is what i understand and this is not some interviewers at MPHASIS,Bangalore does'nt understand.

Thanks and Regards

Nirmal N.
Re: Calling a Trigger from a Procedure. [message #156476 is a reply to message #156465] Wed, 25 January 2006 06:02 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
Nod I think we finally found a common ground, Nirmal Wink You are absolutely right: you cannot call a database trigger from a stored procedure, but you can use 'CALL' from within 'EXECUTE IMMEDIATE'.

MHE
icon4.gif  Re: Calling a Trigger from a Procedure. [message #332776 is a reply to message #156260] Wed, 09 July 2008 12:01 Go to previous messageGo to next message
krishna_ky
Messages: 2
Registered: July 2008
Location: Bangalore
Junior Member

Hi All,

But in my interview also they asked. They said we can call a Pl/Sql trigger in a stored Procedure.

Please any body help me in this. I will check from myside also.

Regards,
KK
Re: Calling a Trigger from a Procedure. [message #332783 is a reply to message #332776] Wed, 09 July 2008 12:47 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
Quite simply incorrect. I'd like to see them turn red in the face when you ask them to show an example of how a trigger can be called from a procedure.

It's the same silliness of saying that you have a sequence associated with a table.
Re: Calling a Trigger from a Procedure. [message #332784 is a reply to message #332776] Wed, 09 July 2008 12:47 Go to previous message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
They said we can call a Pl/Sql trigger in a stored Procedure.

You don't call a trigger, a trigger is automatically executed when an event occurs.

Regards
Michel
Previous Topic: Manual table join error
Next Topic: SEQUENCE
Goto Forum:
  


Current Time: Sun Dec 04 02:48:10 CST 2016

Total time taken to generate the page: 0.05565 seconds