On Delete Cascade

Hi,

For that question, No deletions can leave an order without any associated items - i'm not too sure of the syntax for the on delete cascade option trigger, is this ok:

CREATE OR REPLACE TRIGGER cascade_updates
AFTER UPDATE OF itemid ON item
FOR EACH ROW
BEGIN
UPDATE item
SET item.ordid = :new.ordid
WHERE item.ordid = ld.itemid
END:
/

I dont think this is the correct syntax, could you advise?

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

For this above scenario, the above is I think more than twice in a year, so would the following be ok as a trigger:

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;
/

The itemtot attribute 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
END IF
END
/

I do not think the above is correct, i.e. syntax is incorrect and schema does not correspond to correct tables etc.

Could you advise me on the correct syntax for the above.

Many thanks for all the help you have given me.

Thanks.

-- No deletions can leave an order without any associated items
As i understand your question, You mean that if you remove the parent record then its child/associated record should also be removed!!. If I am correct then you need to define ON DELETE CASCADE constraint in your child table with a foreign key(s)

For example:
CREATE TABLE project (
Project_Id varchar2(3)
constraint project_id_pk PRIMARY KEY,
Project_name varchar2(100))

CREATE TABLE employee (
Project_Id varchar2(3),
Project(Project_Id),
employee_code varchar2(3),
employee_name varchar2(25),
salary Number(7,2),
job_title varchar2(100),
constraint employee_projid_fk
FOREIGN KEY (Project_Id) REFERENCES Project(Project_Id)
ON DELETE CASCADE)

Above table structure implies that if any project is dropped then all employees who were working on this project will also be removed from the child table.

-- No individual user is allowed to update the products associated with a particular order more than twice.
To achieve this target you need to do some extra work for example, how do you know that order is mofied by which user and how many time? If you know that one user is already modified the specific order twice then your trigger will be happy to eliminate any furthur modification on that order. So I think you need to create a table to keep store the required information then write the code as given below:

CREATE TABLE order_info (
Order_no number(10),
username varchar2(30),
no_attempts number(1))

CREATE OR REPLACE TRIGGER check_order_chenge_twice
BEFORE UPDATE ON order
FOR EACH ROW
DECLARE
v_count number(1);
BEGIN
SELECT NVL(no_attempt,0) INTO v_count
FROM order_info
WHERE user name in (SELECT user FROM dual)
AND order_no = :new.order_no;

IF count>=2 THEN
RAISE_APPLICATION_ERROR(-12020,'An oder could not be modified twice');
END IF;

IF count=0 THEN
INSERT INTO order_info values (:new.order_no,SELECT user FROM dual, 1);
END IF;

IF count<2 THEN
UPDATE order_info
SET no_attemp = v_count+1
WHERE order_no = :new order_no
AND username = (SELECT user FROM dual);
END IF;

END;
/

Hi Shafique

Thanks ever so much for all the help you have given me.

One last question, any ideas of how to tackle this one:


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.

Thanks again.