Execute Procedure in a Trigger

Execute procedure in a trigger

Yes, like this:

create or replace trigger trg
after insert on emp
for each row
begin
   myproc(:new.empno, :new.ename);
end;

call procedure from trigger

I replaced your cursors with select intos and changed your procedure to AUTONOMOUS and it will work, but I recommend you to avoid updating the same table in trigger and maybe insert in some other table like: FINALIZED_TRANSACTIONS.

Here is my code for your trigger:

CREATE OR REPLACE TRIGGER INSERT_FINAL_WORKPLACE
   BEFORE UPDATE
   ON TRANSACTIONS
   FOR EACH ROW
BEGIN
   SELECT COUNT (ORDER_ID), COUNT (DECODE (STATE, 'accept', ORDER_ID, NULL))
   INTO VAR_O_ALL, VAR_O_ACT
   FROM TRANSACTIONS
   WHERE ORDER_ID = PI_TRA_ORDER_ID;

   IF VAR_O_ALL = VAR_O_ACT AND :NEW.ORDER_ID IS NULL
   THEN
      CON_API_PKG.CREATEFINISHTRANSACTION (
                                           :NEW.ORDER_ID,
                                           :NEW.PRODUCT_ID,
                                           :NEW.NOTE,
                                           :NEW.REPORTER_ID
                                          );
   END IF;
END;

Here is my code for your procedure:

CREATE OR REPLACE PROCEDURE CREATEFINISHTRANSACTION (
   PI_TRA_ORDER_ID   IN TRANSACTIONS.ORDER_ID%TYPE,
   PI_PRODUCT_ID     IN TRANSACTIONS.PRODUCT_ID%TYPE,
   PI_NOTE           IN TRANSACTIONS.NOTE%TYPE,
   PI_REPORTER_ID    IN TRANSACTIONS.REPORTER_ID%TYPE)
AS
   PRAGMA AUTONOMOUS_TRANSACTION;
   VAR_WORKPLACE_ID   FINAL_WORKPLACE.WORKPLACE_ID%TYPE;
BEGIN
   BEGIN
      SELECT FW.WORKPLACE_ID
      INTO VAR_WORKPLACE_ID
      FROM FINAL_WORKPLACE FW
      WHERE PI_PRODUCT_ID = FW.PRODUCT_ID;
   EXCEPTION
      WHEN OTHERS
      THEN
         --Handle exception by displaying dbms output or insert into a log table
         ROLLBACK;
   END;

   INSERT
   INTO TRANSACTIONS (
                      TRANSACTION_ID,
                      PRODUCT_ID,
                      CREATED,
                      NOTE,
                      WORKPLACE_ID,
                      REPORTER_ID,
                      ORDER_ID,
                      PHASE
                     )
      VALUES (
                (SELECT MAX (TRANSACTION_ID) FROM TRANSACTIONS) + 1,
                PI_PRODUCT_ID,
                CURRENT_TIMESTAMP,
                PI_NOTE,
                VAR_WORKPLACE_ID,
                PI_REPORTER_ID,
                PI_TRA_ORDER_ID,
                'final');

   COMMIT;
EXCEPTION
   WHEN OTHERS
   THEN
      --Handle exception by displaying dbms output or insert into a log table
      ROLLBACK;
END; 

How to call procedure using triggers

Basically, you could do everything within a trigger, but OK - that's some kind of a homework. Here's how I understood it.

Procedure doesn't do anything "smart", just displays the message. Note that DBMS_OUTPUT.PUT_LINE call displays a message, it doesn't prevent anyone to do anything - instead of it, you should raise_application_error.

The answer to your question

should I write insert/update DML statement?

is - in my opinion - NO, you shouldn't.

---

Trigger calls that procedure. Yours doesn't check the month, while it should (i.e. move that control from procedure to trigger).

---

Everything put together might look like this:

SQL> create or replace procedure secure_dml
  2  is
  3  begin
  4    raise_application_error(-20000,
  5      'You can modify or add a department only at the end of a financial year');
  6  end;
  7  /

Procedure created.

SQL> create or replace trigger tr_check_dept
  2    before insert on department
  3    for each row
  4  begin
  5    if extract(month from sysdate) <> 3 then
  6       secure_dml;
  7    end if;
  8  end;
  9  /

Trigger created.

SQL> insert into department(deptno, deptname, deptloc)
  2    values (4, 'D', 'W');
insert into department(deptno, deptname, deptloc)
            *
ERROR at line 1:
ORA-20000: You can modify or add a department only at the end of a financial year
ORA-06512: at "SCOTT.SECURE_DML", line 4
ORA-06512: at "SCOTT.TR_CHECK_DEPT", line 3
ORA-04088: error during execution of trigger 'SCOTT.TR_CHECK_DEPT'

SQL>

---

Just for testing purposes, as it is September today, let's modify trigger code so that it works for this month (instead of March) and see what INSERT does in that case.

SQL> create or replace trigger tr_check_dept
  2    before insert on department
  3    for each row
  4  begin
  5    if extract(month from sysdate) <> 9 then      --> this line was changed
  6       secure_dml;
  7    end if;
  8  end;
  9  /

Trigger created.

SQL> insert into department(deptno, deptname, deptloc)
  2    values (4, 'D', 'W');

1 row created.

SQL>

Right; now it works.

SQL Trigger to Call a Stored Procedure with inserted value as parameter

You can user a trigger like this:

CREATE TRIGGER TR_Monitor_BatchDetails_Inserts 
    ON BatchDetails
AFTER INSERT
AS 
BEGIN 
    DECLARE @BatchId INT
    SELECT TOP 1 @BatchId = BatchId
    FROM    INSERTED
    ORDER BY [PK_ColumnHere/Date/OtherOrderingColumn] DESC

    EXEC    SomeStoredProc @BatchId
END

But there are a few flaws with it:

1 - there CAN be more rows in INSERTED (rows that are being inserted) and this wont handle all of them

2 - you may want to call your procedure from where the inserts are being made into BatchDetails table. Maybe have a proc that does the inserts, and add there the call to this your procedure. That will be a better way to approach this.

Hope it makes sense

Call Stored Procedure within Create Trigger in SQL Server

Finally ...

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON

GO
ALTER TRIGGER [dbo].[RA2Newsletter] 
   ON  [dbo].[Reiseagent] 
   AFTER INSERT
 AS
    declare
    @rAgent_Name nvarchar(50),
    @rAgent_Email nvarchar(50),
    @rAgent_IP nvarchar(50),
    @hotelID int,
    @retval int

BEGIN
    SET NOCOUNT ON;

    -- Insert statements for trigger here
    Select 
        @rAgent_Name = rAgent_Name,
        @rAgent_Email = rAgent_Email,
        @rAgent_IP = rAgent_IP,
        @hotelID = hotelID
    From Inserted
    
    EXEC insert2Newsletter 
        '',
        '',
        @rAgent_Name,
        @rAgent_Email,
        @rAgent_IP,
        @hotelID,
        'RA',
        @retval
END

---

Related Topics