Help creating a new trigger!

[EM1107]

Good everyone!

I am new to the Oracle world and I need to create a trigger but I am having a problem with it. The trigger I need as to verify a value in my table 1 and if that value is null or set to 0 than I need to change the value to 1 and verify if my table 1 already have information it it, referencing the ID of the first table.

Here is what I am trying to place in.

CREATE OR REPLACE TRIGGER CHECK_SEC_BI
BEFORE INSERT ON PROFILE
FOR EACH ROW
BEGIN
IF :NEW.DEFAULT_RIGHTS:=1

THEN
INSERT INTO SECURITY (THING, PERSONORGROUP, ACCESSRIGHTS)
VALUES NEW.SYSTEM_ID, 1, 255)

ELSE
IF :NEW.DEFAULT_RIGHTS<>1
THEN
:NEW.DEFAULT_RIGHT:=1

INSERT INTO SECURITY (THING, PERSONORGROUP, ACCESSRIGHTS)
VALUES NEW.SYSTEM_ID, :NEW.TYPIST , 255);

INSERT INTO SECURITY (THING, PERSONORGROUP, ACCESSRIGHTS)
VALUES NEW.SYSTEM_ID, 1, 255);

END IF;
END;

What am I doing wrong here!

 

[LKBrwnDBA]

1) You are missing one 'END IF;'

2) If :NEW.DEFAULT_RIGHTS can be NULL, then you nee to use NVL() function:

IF NVLNEW.DEFAULT_RIGHTS,-1):=1

3) This is a confusing statement: "to verify a value in my table 1 and if that value is null or set to 0 than I need to change the value to 1 and verify if my table 1 already have information it it, referencing the ID of the first table."

Please re-phrase and/or provide concise information.



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

 

[BJCooperIT]

It also would be helpful if you post all the error messages you are receiving since my crystal ball is in the shop.

IF :NEW.DEFAULT_RIGHTS[COLOR=red]:[/color]=1

is incorrect, try:

IF :NEW.DEFAULT_RIGHTS = 1

^{Beware of false knowledge; it is more dangerous than ignorance. ~George Bernard Shaw}
Consultant Developer/Analyst Oracle, Forms, Reports & PL/SQL (Windows)
My website: Emu Products Plus

 

[EM1107]

As you mentioned in that question 3.

3) This is a confusing statement: "to verify a value in my table 1 and if that value is null or set to 0 than I need to change the value to 1 and verify if my table 1 already have information it it, referencing the ID of the first table."

When my entry does include a 1 instead of a 0 or null
I have to verify a different table to see if there is already information inserted that reference the insert I need to do in it. If I have to delete it before I can insert it again.

 

[LKBrwnDBA]

Please clarify:

When my entry (in PROFILE table?) does include a 1 instead of a 0 or null I have to verify a different table (which table?) to see if there is already information inserted that reference the insert (which columns are referenced?) I need to do in it. If I have to delete it before I can insert it again (why delete-insert? what about update?).



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

 

[EM1107]

OK!

The profile is my primary table name. The table I need to reference to is called security. Some time when the user place information in profile there is an entry that get place in the security table, but that entry does not always get deleted when the user decide to change it. This may create duplicate entry. I need to verify that the entry does not already exits before I add it back in.

I need to have an entry for the supervisor at all time on every entry done in the profile table. The problem is that the user can change the security an remove the supervisor entry from the security. I need to have it back in no matter what the user will do.

 

[LKBrwnDBA]

One method is INSERT/UPDATE, kinda like this:

...etc...
  BEGIN
    INSERT INTO SECURITY (THING, PERSONORGROUP, ACCESSRIGHTS)
     VALUES (:NEW.SYSTEM_ID, :NEW.TYPIST , 255);
  EXCEPTION
    WHEN DUP_VAL_ON_INDEX THEN
      UPDATE SECURITY 
         SET PERSONORGROUP = :NEW.TYPIST
           , ACCESSRIGHTS = 255
       WHERE THING = :NEW.SYSTEM_ID;
  END;
  ...etc...

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

 

[LKBrwnDBA]

PS: To avoid duplicate entries create a Primary (or Unique) key for the "SECURITY" table.

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