Create trigger.

[EM1107]

I am new to Oracle and I am trying to create a trigger.
In my trigger I have the following line and Oracle tell me that this not correct. What is wrong with that section.

CREATE OR REPLACE TRIGGER CHECK_SEC_AI
AFTER INSERT ON PROFILE
FOR EACH ROW
BEGIN
IF (SELECT THING, PERSONROGROUP FROM SECURITY WHERE THING= :OLD.SYSTEM_ID AND PERSONORGROUP = 0) <>NULL
THEN
DELETE FROM SECURITY WHERE THING = :OLD.SYSTEM_ID
AND PERSONORGROUP = 0;
END IF;
END;

What I want to do is verify if the entry exist in my security table and delete it if it does as it should not be there. The system_id is the primary key that reference the entry in my profile table and personorgroup is an Id that reference the primary id from an other table. So if there is an entry in my security table having a thing 1111 and a group 0 I want to delete it.

 

[SantaMufasa]

Here is how I would code your trigger (Note: Since I must run to a meeting, I haven't the time to run the syntax against Oracle-PL/SQL, but it should be fine.):

CREATE OR REPLACE TRIGGER CHECK_SEC_AI
AFTER INSERT ON PROFILE
FOR EACH ROW
BEGIN
    for x in (SELECT THING, PERSONROGROUP
                FROM SECURITY
               WHERE THING= :OLD.SYSTEM_ID
                 AND PERSONORGROUP = 0) loop
        DELETE FROM SECURITY
         WHERE THING = :OLD.SYSTEM_ID
           AND PERSONORGROUP = 0;
    end loop;
    commit;
end;

Let us know how this works for you.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]

 

[LKBrwnDBA]

Why not just the 'DELETE'?:

CREATE OR REPLACE TRIGGER CHECK_SEC_AI
AFTER INSERT ON PROFILE
FOR EACH ROW
BEGIN
  DELETE FROM SECURITY
   WHERE THING = :OLD.SYSTEM_ID
     AND PERSONORGROUP = 0;
END;

----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[EM1107]

You are correct to! I could use that but I also need to verify if the entry already exist!

For instance I have a group id using the number 1.
if that group already exist in my security table for the ld.system_id I do not want to delete it, I just want to change the value of my accessrights column to 255 no matter what value was there before.