| Need to implement this business rule for triggers [message #38782] |
Tue, 14 May 2002 12:24  |
Sid
Messages: 38
Registered: May 1999
|
Member |
|
|
Oracle gives me this error:
CREATE or REPLACE TRIGGER salesman_com_only
*
ERROR at line 1:
ORA-06550: line 13, column 4:
PLS-00103: Encountered the symbol ";" when expecting one of the following:
if
Please first tell me if my code is correct for the following business rule I need:
"Those employers whose job role is 'SALESMAN' always receive commission. Employers who are not salesman should never receive a commission"
CREATE or REPLACE TRIGGER salesman_com_only
BEFORE UPDATE or INSERT on empy
FOR EACH row
BEGIN
IF INSERTING THEN
IF :new.job = 'SALESMAN' THEN
:NEW.COMM :=0;
ELSE --NEW JOB IS A ROLE THAT IS NOT SALESMAN
--SO DO NOT ALLOW COMM VALUE
IF :new.com <> null THEN
RAISE_APPLICATION_ERROR(-20501, 'Only salesman can have a commission');
END IF;
END IF;
END;
/
CREATE or REPLACE TRIGGER salesman_com_only
*
ERROR at line 1:
ORA-06550: line 13, column 4:
PLS-00103: Encountered the symbol ";" when expecting one of the following:
if
|
|
|
|
| Re: Need to implement this business rule for triggers [message #38784 is a reply to message #38782] |
Tue, 14 May 2002 15:00  |
Todd Barry
Messages: 4819
Registered: August 2001
|
Senior Member |
|
|
You have specified that the trigger fires on insert or update, but your logic only handles inserts. Here I changed it to fire on insert only, but if you need the check on update, make sure to also look at 'if updating' in your trigger body.
1) You were missing an 'end if;' to go with the 'if inserting' (which is now gone in my version).
2) Never check whether a value is = or <> to null. A null value check can only be done as 'is null' or 'is not null'.
create or replace trigger salesman_com_only
before insert on empy
for each row
begin
if :new.job = 'SALESMAN' then
:new.comm := 0;
else
--NEW JOB IS A ROLE THAT IS NOT SALESMAN, SO DO NOT ALLOW COMM VALUE
if :new.comm is not null then
raise_application_error(-20501, 'Only salesman can have a commission');
end if;
end if;
end;
/ |
|
|
|
| Re: Need to implement this business rule for triggers [message #38798 is a reply to message #38782] |
Wed, 15 May 2002 11:21 |
Sid
Messages: 38
Registered: May 1999
|
Member |
|
|
Todd,
Thank you for the replies on if statement and also on the null statement.
I have included the code logic to handle updates, here is the new error:
create or replace trigger salesman_com_only
*
ERROR at line 1:
ORA-06550: line 16, column 7:
PLS-00103: Encountered the symbol ";" when expecting one of the following:
begin declare exit for goto if loop mod null pragma raise
return select update while <an identifier>
<a double-quoted delimited-identifier> <a bind variable>
<a single-quoted SQL string> << close delete fetch lock
insert open rollback savepoint set sql commit
The symbol "exit" was substituted for ";" to continue.
On line 16 I don't want any action taken since the employee is a salesman and thus we should be able to allow for a commission.
Again please let me know if my code logic is correct for this business logic:
"Those employers whose job role is 'SALESMAN' always receive commission. Employers who are not salesman should never receive a commission"
Here is the new code:
create or replace trigger salesman_com_only
before insert or update on empy
for each row
begin
if inserting then
if :new.job = 'SALESMAN' then
:new.comm := 0;
else --NEW JOB IS A ROLE THAT IS NOT SALESMAN, SO DO NOT ALLOW COMM VALUE
if :new.comm is not null then
raise_application_error(-20501, 'Only salesman can have a commission');
end if;
end if;
end if;
if updating then
if :old.job='salesman' then
--ALLOW UPDATE OF COMMISSION IT IS A SALESMAN
else -- THE EMPLOYER IS NOT A SALESMAN SO DO NOT ALLOW COMMISSION
if :new.comm is not null then
raise_application_error(-20501, 'You are not a salesman, you cannot have a commission'); end if;
end if;
--NEED TO CHECK ALSO IF AN UPDATE IS BEING MADE WHERE WORKER CHANGES JOB ROLE
--THE WORKER IS NOW A SALESMAN SO HE SHOULD BE ALLOWED FOR A COMMISION.
if :new.job = 'SALESMAN' then
:new.comm := 0;
end if;
end if;
end;
/
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
