Oracle Trigger Query

Hi

I am new to using triggers, and have a number of constraints I need to implement with some queries I have. i am using the Scott database with Oracle 9i.

For the following, I need to implement some database triggers, but seem to be going round in circles.

The following job updates are not allowed: ANALYST <> SALESMAN.

CREATE OR REPLACE TRIGGER check_job
BEFORE INSERT OR UPDATE OF job ON emp
FOR EACH ROW
WHEN(new.job <> ‘ANALYST’)
AND(new.job <> 'SALESMAN')
DECLARE
v_job emp.job%TYPE;
BEGIN
SELECT job
FROM emp
WHERE job = :new.job;
IF :new.jobl <> 'ANALYST' OR
:new.job <> 'SALESMEN' THEN
RAISE_APPLICATION_ERROR(-20101,’Update cannot be done’);
END IF;
END;
/


No new orders are to be accepted from customers in the city of BURLINGAME.

CREATE OR REPLACE TRIGGER secure_customer
BEFORE INSERT OR UPDATE OR DELETE ON customer
BEGIN

Can anyone advise me on the above, any help would be greatly appreciated.

Thanks

Your first trigger, should be look like that:

CREATE OR REPLACE TRIGGER check_job
BEFORE INSERT OR UPDATE OF job ON emp
FOR EACH ROW
WHEN(new.job IN ('ANALYST','SALESMAN'))
DECLARE
v_job emp.job%TYPE;
BEGIN
IF :new.job <> 'ANALYST' OR
:new.job <> 'SALESMEN' THEN
RAISE_APPLICATION_ERROR(-20101,'Update cannot be done');
END IF;
END;


and the second trigger need the same kind of implementation as the first trigger.

you can make some changes into above code to enhance the effeciency of trigger.

Hi

Thats great, I have some more that I need to implement, any chance you could have a look at them for me, i.e tell me what type of trigger it is would need etc...as I have come into this totally blind.


Q) No individual user is allowed to update the products associated with a particular order more than twice.

Q) Any update or insert into the SALGRADE table has to ensure that salary ranges for each grade do not overlap.

Q) The itemtot attribute needs to be automatically maintained and not be directly updateable.

Any help you could give with these, would be truly appreciated.

Basically of which trigger is best for which question and how it should be structured.

Thanks.