I am using the scott database, ie. emp, dept, ord, product, item table and need to create some triggers to implement certain constraints. These are:
(1) No deletions can leave an order without any associated items.
For this I have created a table and have also acknowledged that I need a delete cascade option, howver I am unsure of the syntax, coding ect for this type of trigger, could someone advise i.e. provide an example of this?
(2) 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.
I am completely unsure with this one as to what type of trigger is required and what actual schema from the scott database would be used. The information regarding this constraint is very restricted and does not give me a lot of scope to work with.
If there is anyone whoc ould help me with the above two constraints I would be very grateful.
1) This is not the DELETE CASCADE rule, it is something else. It is saying that if an Order has n Order Lines, then you may only delete up to n-1 Order Lines from it - i.e. you must leave at least 1 Order Line on it. Or perhaps, alternatively, it means that when the last Order Line it deleted, you must ensure that the Order is also deleted. This could be achieved via an AFTER DELETE trigger on the Order Lines table.
2) The summary column could be maintained by an AFTER INSERT OR UPDATE OR DELETE trigger on the detail table. To ensure that users could not update it directly, you can specify the list of columns they can update in the GRANT statement, and leave out that column: