Oracle TRIGGER to set 'RESTRICTION' column on table 'LRESTRICTION' to 'YES&-CodePudding

Im trying to create a trigger on oracle sql that updates the 'RESTRICTION' column of 'LRESTRICTION' table to display 'YES' or 'NO' based on the value of 'LAMOUNT' column of 'LEMBER' table when 'LAMOUNT' = 5, I have tried a few other ways but I cant seem to understand how to actually set a trigger properly as there always seems to be errors. I am new to sql and having a hard time understanding triggers as the error messages are also confusing to me they dont seem to be as straight forward as in other languages. My question here is how can I create a trigger that does what Im trying to do or is it even possible?

My tables look like this:


CREATE TABLE LMEMBER ( 
FNAME VARCHAR2(10), 
LNAME VARCHAR2(10), 
MTYPE VARCHAR2(7), 
IDNUM NUMBER(6) NOT NULL PRIMARY KEY, 
LAMOUNT NUMBER(2), 
LDURATION NUMBER(3)
);

CREATE TABLE LRESTRICTION(
ID_NUM NUMBER(6) REFERENCES LMEMBER(IDNUM),
RESTRICTION VARCHAR2(3)
);

-------##########------ And my trigger looks like this:

CREATE OR REPLACE TRIGGER SET_RES
AFTER INSERT OR UPDATE ON LMEMBER

FOR EACH ROW 
BEGIN
    IF: NEW.LAMOUNT:5
        UPDATE LRESTRICTION 
            INSERT INTO LRESTRICTION(RESTRICTION)VALUES('YES')
    END IF;          
END;

Error is get from this:


Errors: TRIGGER SET_RES
Line/Col: 2/20 PLS-00103: Encountered the symbol "" when expecting one of the following:

   . ( * @ % & = -   < / > at in is mod remainder not rem then
   <an exponent (**)> <> or != or ~= >= <= <> and or like like2
   like4 likec between || indicator multiset member submultiset

Line/Col: 2/20 PLS-00049: bad bind variable '5'

also tried more triggers with SET RESTRICTION = 'YES' instead of INSERT INTO LRESTRICTION(RESTRICTION)VALUES('YES') and also tried 'WHEN' and 'WHERE' clauses instead of IF but I cant seem to make it work

CodePudding user response：

You have typos in your IF clause. And the syntax for an UPDATE is UPDATE table SET column = <something> WHERE <some condition>.

CREATE OR REPLACE TRIGGER set_res
AFTER INSERT OR UPDATE ON lmember
FOR EACH ROW 
BEGIN
    IF :new.lamount = 5 THEN
        UPDATE lrestriction 
        SET restriction = 'YES'
        WHERE id_num = :new.idnum;
    END IF;          
END;