Query on Triggers..

Question

Created One Trigger in Oracle..

SQL> CREATE OR REPLACE TRIGGER student_after_insert
  2  AFTER INSERT
  3  ON student
  4  FOR EACH ROW
  5  BEGIN
  6     @hello.pl
  9  END student_after_insert;
 10  /

Contents of hello.pl are:-

BEGIN
   DBMS_OUTPUT.PUT_LINE('hello world');
END;

And.. the result is pretty good, as the content of hello.pl is displayed on screen while inserting a record..

Now, the query is -- When i change the content of the hello.pl file, after exiting from oracle, and then logging again, It doesn't shows the updated contents, instead it shows the previous content..

I noticed that, if i drop the trigger and create it again, then it is working fine.. Why is it happening so.. And what is the solution to this problem..

Answer 1

@hello.pl simply includes the text of hello.pl in the body of the CREATE...TRIGGER command. There is no link from Oracle to the file.

Note that, in the general case, the Oracle server is on another machine and has no access to the contents of your hard drive.

Answer 2

It happens because the @ works much like the #include in a c/c++ preprocessor, that is, SQL*Plus inserts the content of the file hellp.pl at compile time.

If you want to output the content of a file when the trigger is fired, you might want to look into utl_file.

But it's probably easier for you to create a package similar to

create or replace package trigger_content as 
  text varchar2(100); 
end;
/

You can then dynamically alter the value of text:

 exec trigger_content.text := 'hello world';

and print the value of text with

dbms_output.put_line(trigger_content.text);

However, the latter "solution" doesn't work across sessions.

Answer 3

Look into EXECUTE IMMEDIATE and DBMS_SQL for dynamic coding.