Major Trigger Confusion

I am using Oracle 9i using the Scott database and need to implement some constraints into the database, however I am having major trouble with the syntax and coding of these triggers enough to satisfy the scenario. These are the ones I have tried to satisfy, however I am totally unsure of what the syntax is for the triiger to work.

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

CREATE OR REPLACE TRIGGER check_custid
BEFORE UPDATE OF custid ON ord
FOR EACH ROW
WHEN (city = 'BURLINGAME')
BEGIN
IF :city = 'BURLINGAME' THEN
RAISE_APPLICATION_ERROR(-20101,'Cannot accept new order for city of BURLINGAME');
ELSE DISPLAY(‘Update unsuccessful’);
END IF;
END;
/

Q2) No deletions can leave an order without any associated items

CREATE OR REPLACE TRIGGER UpdateItemid
AFTER INSERT OR DELETE OR UPDATE on Ord

DECLARE

CURSOR c_Itemordid IS
SELECT itemid
FROM Item
GROUP BY ordid;

BEGIN
display ('Trigger delete_itemid in progress');

FOR v_ItemRow in c_Itemid LOOP
DELETE itemid

Q3) Any new department based in ‘HOUSTON’ is to have the string ‘OIL’ somewhere in the department’s name.

CREATE OR REPLACE TRIGGER check_dept
BEFORE INSERT OR UPDATE OF loc ON dept
FOR EACH ROW
WHEN(new.dept <> ‘HOUSTON’)
DECLARE
v_OILdept.loc%TYPE;
BEGIN
SELECT loc,
INTO v_loc
FROM dept
WHERE loc = :'HOUSTON';
IF :deptname = 'OIL'
RAISE_APPLICATION_ERROR(-20102’Update succeeded’);
END IF;
END;
/

Q4) The itemtot attribute (the inclusion of which in the database is evidence of very bad design) needs to be automatically maintained and not be directly updateable.

CREATE OR REPLACE TRIGGER check_itemtot
AFTER UPDATE OF itemtot ON Item
DECLARE
v_itemtot_changes NUMBER;
BEGIN
SELECT upd, max_itemtot
INTO v_itemtot_changes
FROM audit_table


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

CREATE OR REPLACE TRIGGER secure_Ord
BEFORE INSERT OR UPDATE OR DELETE ON Ord
BEGIN
IF (To_char(Ordid, 'prodid') IN ('ITEM', 'ORD')
THEN
IF DELETING
THEN RAISE_APPLICATION_ERROR(-20103, You may only update
the products more than twice');

END IF
ENDIF;
END;
/

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

CREATE OR REPLACE TRIGGER check_salary_count
AFTER UPDATE OR INSERT OF grade ON salgrade
DECLARE
v__grade_changes NUMBER
v_sal_changes NUMBER;
BEGIN
SELECT upd, max_upd
INTO v_grade_changes, v_max_changes
FROM audit_table
WHERE user_name = user
AND tablename = ‘SALGRADE’
AND column_name = ‘GRADE’

IF v_salary_changes > v_max_changes THEN
RAISE_APPLICATION_ERROR(-20104, ‘Salary Ranges for each grade must not overlap')
END IF;
END;
/

If someone could please help me with the coding, syntax etc I would be ever so grateful - am using this forum as a last resort as I have been tring to do these correctly for the past week!

Please help!

Thanks.

I have read your triggers, you are writting in right direction but you make only few miner mistakes, You can use the Oracle online documentaion, provided by the Oracle corp.
without any cost, just free registration is required. Please visit http://tahiti.oracle.com

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

CREATE OR REPLACE TRIGGER check_custid
BEFORE INSERT ON ord -- This trigger only fires when you try to insert any record in BERLINGAMEFOR EACH ROW
WHEN (new.city = 'BURLINGAME')
BEGIN
IF :city = 'BURLINGAME' THEN -- junk code remove it
RAISE_APPLICATION_ERROR(-20101,'Cannot accept new order for city of BURLINGAME');
ELSE DISPLAY(‘Update unsuccessful&#8217; -- junk code remove it
END IF; -- junk code remove it
END;
/

Q2) No deletions can leave an order without any associated items

The best solution for this use inegrity constraint like Primary key/foreign key with ON DELETE CASCADE option

Q3) Any new department based in ‘HOUSTON’ is to have the string ‘OIL’ somewhere in the department’s name.

CREATE OR REPLACE TRIGGER check_dept
BEFORE INSERT OR UPDATE OF loc ON dept
FOR EACH ROW
WHEN(new.city = 'HOUSTON')
DECLARE
v_count number := 0;
BEGIN

SELECT '1' INTO v_count
FROM dual
WHERE :new.department like '%OIL%';

IF v_count <> 1 THEN
RAISE_APPLICATION_ERROR(-20102,'Update can not be taken place');
ELSE
DBMS_OUTPUT.PUT_LINE('Update succeeded');
END IF;
END;
/
Q5) No individual user is allowed to update the products associated with a particular order more than twice

twice within a day, week, month, year or for ever?????

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

CREATE OR REPLACE TRIGGER check_salary_count
AFTER UPDATE OR INSERT ON salgrade
FOR EACH ROW
DECLARE
v_count number := 0
BEGIN
SELECT 1 INTO v_count
FROM SALGRADE
WHERE EXISTS (SELECT '1' FROM SALGRADE
WHERE losal = :new.losal
AND hisal = :new.hisal);
IF v_salary_changes > v_max_changes THEN
RAISE_APPLICATION_ERROR(-20104, ‘Salary Ranges for each grade must not overlap')
END IF;
END;
/

You could make this code more efficient.

sorry, correct the following queries.

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

CREATE OR REPLACE TRIGGER check_salary_count
AFTER UPDATE OR INSERT ON salgrade
FOR EACH ROW
DECLARE
v_count number := 0
BEGIN
SELECT 1 INTO v_count
FROM SALGRADE
WHERE EXISTS (SELECT '1' FROM SALGRADE
WHERE losal = :new.losal
AND hisal = :new.hisal);
IF v_count = 1 THEN
RAISE_APPLICATION_ERROR(-20104, ‘Salary Ranges for each grade must not overlap')
END IF;
END;
/

Q3) Any new department based in ‘HOUSTON’ is to have the string ‘OIL’ somewhere in the department’s name.

CREATE OR REPLACE TRIGGER check_dept
BEFORE INSERT OR UPDATE OF loc ON dept
FOR EACH ROW
WHEN(new.city = 'HOUSTON')
DECLARE
v_count number := 0;
BEGIN

SELECT 1 INTO v_count
FROM dual
WHERE :new.department like '%OIL%';

IF v_count <> 1 THEN
RAISE_APPLICATION_ERROR(-20102,'Update can not be taken place');
ELSE
DBMS_OUTPUT.PUT_LINE('Update succeeded');
END IF;
END;
/