3 Replies Latest reply: Jun 7, 2011 3:17 AM by 780335 RSS

    how to create a trigger that call a procedure or a function

    865487
      Hi i have been trying to create a trigger that calls a procedure
      this what i've been trying without luck so far
      when user insert new data set the trigger which the trigger calls a procedure

      create or replace procedure helloworld
      as
      x varchar2(20);
      begin
      x := 'hello world';
      dbms-output.put_line(x);
      end;
      /


      and this is the trigger

      create or replace trigger salary
      after insert or update ON authors
      begin
      helloworld; (this the procedure name)
      end;
      /

      CREATE TABLE authors (
      id NUMBER PRIMARY KEY,
      first_name VARCHAR2(50),
      last_name VARCHAR2(50)
      );

      Table created.

      INSERT INTO authors (id, first_name, last_name)
      VALUES (1, 'Marlene', 'Theriault');

      no luck not working the trigger
        • 1. Re: how to create a trigger that call a procedure or a function
          Frank Kulash
          Hi,
          user10683509 wrote:
          Hi i have been trying to create a trigger that calls a procedure
          this what i've been trying without luck so far
          when user insert new data set the trigger which the trigger calls a procedure
          Did you get any error messages when you compiled the procedure or the trigger? if so, post the complete error message(s).
          create or replace procedure helloworld
          as
          x varchar2(20);
          begin
          x := 'hello world';
          dbms-output.put_line(x);
          end;
          /
          You need an underscore, not a hyphen, in dbms_ouptut.
          and this is the trigger

          create or replace trigger salary
          after insert or update ON authors
          begin
          helloworld; (this the procedure name)
          end;
          /
          If you want a comment at the end of a line, put 2 hyphens before the comment, like this:
          helloworld;         -- (this the procedure name)
          CREATE TABLE authors (
          id NUMBER PRIMARY KEY,
          first_name VARCHAR2(50),
          last_name VARCHAR2(50)
          );

          Table created.
          I'd create the table first, then create the trigger.
          INSERT INTO authors (id, first_name, last_name)
          VALUES (1, 'Marlene', 'Theriault');

          no luck not working the trigger
          Did you say
          SET  SERVEROUTPUT  ON
          first? If you didn't, output from dbms_output will not appear.

          Dbms_output is handy for certain jobs, like debugging, but it's probably not the best way for sending messages to users in your production environemnt. What front end are you using? You should use its display features, perhaps storing messages in a table or SYS_CONTEXT variables. What is the business purpose here? What exactly are you trying to do?

          Triggers and procedures are features of PL/SQL, not SQL*Plus. You'll probably get better results faster if you post questions like this in the SQL and PL/SQL forum:
          PL/SQL
          • 2. Re: how to create a trigger that call a procedure or a function
            865487
            Hi thank you very much for trying to help me i did manage to get a triggers to call procedure which is

            create or replace trigger salary
            after insert or update ON authors
            for each row
            begin
            procedure_name;
            end;
            /

            once again thanks, even doe this a different topic that i've post i thought i might ask. if possible can you have a look at my other post its called

            thread
            (how do you run procedure with declare on sql plus)

            has i do not know how to exec it sorry for asking about different topic has i havent got no answer on that topic above.
            thanks you
            • 3. Re: how to create a trigger that call a procedure or a function
              780335
              hi

              Edited by: mithun on Jun 7, 2011 1:17 AM