CODEWITHSUNDEEP
oracle-databaseplsqltriggerstoad
Al007
Al007

Reputation: 383

Oracle trigger gives unknown error

I have this SQL trigger that working fine:

    USE [DMS_TEST]
GO

IF EXISTS (SELECT object_name(id)  FROM sysobjects
   WHERE xtype ='TR' AND object_name(id) = N'TR_SALESSTATUS_CHANGE')   
   DROP TRIGGER [TR_SALESSTATUS_CHANGE]
GO

CREATE TRIGGER [TR_SALESSTATUS_CHANGE] ON [VM_SDetail] 
FOR UPDATE 
AS 
BEGIN
        Set NoCount On
        UPDATE          [VM_SDetail] 
        SET             SALESSTATUS = 1 
        From            [VM_SDetail]
                        [VM_SDetail] 
        INNER JOIN      INSERTED 
        ON              [VM_SDetail].ID = INSERTED.ID 
        Where           (Not Inserted.ValueVal Is NULL AND  (Inserted.ValueVal <> '')) 
        AND             Inserted.SALESSTATUS = 0;
End
GO

I converted this trigger to Oracle:

CREATE OR REPLACE TRIGGER TR_SALESSTATUS_CHANGE AFTER UPDATE
ON DMS_TEST.VM_SDetail 
FOR EACH ROW 
BEGIN
        UPDATE          DMS_TEST.VM_SDetail 
        SET             SaleStatus = 1 
        From            DMS_TEST.VM_SDetail
        INNER JOIN      :NEW 
        ON              DMS_TEST.VM_SDetail.ID = :NEW.ID 
        Where           (Not :NEW.ValueVal Is NULL AND  (:NEW.ValueVal <> '')) 
       AND             :NEW.SalesStatus = 0;
End;

I am getting this warning and trigger not working as expected:

Warning: TRIGGER created with compilation errors.

I am using TOAD. I tried every thing for two days now. I tried to eliminate and simplify the trigger but it is not working even with an simple update command. Any ideas about the bug or any tools that could give me more error messages or details?

Thanks

Upvotes: 0

Views: 337

Answers (2)

Justin Cave
Justin Cave

Reputation: 231771

First, if you type "show errors" in SQL*Plus, you'll get the actual errors. Second, you can't join to the :new pseudo-record. It's not a table. Third, you almost certainly don't want to query VM_SDetail in a row-level trigger on VM_SDetail. Assuming that id is the primary key for your table, my guess is that you just want to set the :new.SaleStatus

CREATE OR REPLACE TRIGGER TR_SALESSTATUS_CHANGE AFTER UPDATE
  ON DMS_TEST.VM_SDetail 
  FOR EACH ROW 
BEGIN
  IF(    (Not :NEW.ValueVal Is NULL AND  (:NEW.ValueVal <> '')) 
     AND :NEW.SalesStatus = 0 )
  THEN
    :new.SalesStatus := 1;
  END IF;
END;

You could also put the IF statement in the WHEN clause of the trigger so that the trigger doesn't actually fire unless the condition is met. Then the trigger body would just be the :new.SalesStatus := 1; line.

I should also point out that in Oracle, the empty string is equivalent to NULL so there is no point to checking that ValueVal is both NOT NULL and not equal to the empty string. It would make more sense to just say

IF( :new.ValueVal IS NOT NULL and
    :new.SalesStatus = 0 )
THEN

Upvotes: 2

Michael S.
Michael S.

Reputation: 1791

Execute it with F9 (Execute/compile statement at caret). It's the leftmost button on the Editor toolbar that has the green play button. You're executing it with F5, as script. That requires SHOW ERRORS as Justin Cave points out, but execute as script should be reserved for script execution (more than 1 statement) or when you want to emulate SQL*Plus behavior. Single statement execution, compiling objects, etc. is best done using F9. It automatically fetches errors among many other things.

Upvotes: 2

Related Questions