This discussion is archived
3 Replies Latest reply: Jun 7, 2011 1:17 AM by 780335 RSS

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

865487 Newbie
Currently Being Moderated
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 Guru
    Currently Being Moderated
    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:
    SQL and PL/SQL
  • 2. Re: how to create a trigger that call a procedure or a function
    865487 Newbie
    Currently Being Moderated
    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 Explorer
    Currently Being Moderated
    hi

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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points