Triggers: Using INSTEAD OF

[Byrd24]

Can someone please help me with the following problem:

SQL> create table guidry_l
2 (student_id number(4) not null,
3 first_name varchar2(25),
4 last_name varchar2(25),
5 address varchar2(40),
6 zip number(5),
7 phone varchar2(12),
8 registration_date date not null,
9 constraint guidry_l_pk primary key(student_id));


SQL> create or replace view lguidry_view as
2 select student_id, registration_date
3 from guidry_l;


I have to create an INSTEAD OF trigger defined on the view created above. This trigger will update the student w/ student_id of 1000 and set the registration date to August 12, 2003.

My textbook doesn't really explain how to create an INSTEAD OF trigger using the UPDATE.

[DNAunion2000]

/*DNAunion*/ I don't have a solid answer, just an example I found in one of my books. But first, the reason many familiar with SQL might not be able to help... While explaining the syntax for triggers, the author states:

"1. The <before or after> specification is either BEFORE or AFTER (the SQL standard does not support INSTEAD OF, though some products do)." (C. J. Date, An Introduction to Database Systems: Eighth Edition, Pearson Education, 2004, p283)

As for the one example I found in the book... (Note that the "quote" I provide has been changed slightly -- but not materially -- to improve clarity).



CREATE VIEW london_supplier
AS SELECT supplierID, sname, status
FROM supplier
WHERE city = 'London'


Normally, if the user tries to insert a row into this view, SQL will actually insert a row into the underlying Supplier table with City value whatever the default is for the City column. Assuming that default is not 'London', the net effect is that the new row will not appear in the view! Let us therefore create a triggered procedure as follows:


CREATE TRIGGER london_supplier_insert
INSTEAD OF INSERT ON london_supplier
REFERENCING NEW ROW AS R
FOR EACH ROW
INSERT INTO supplier (supplierID, sname, status, city)
VALUES (r.supplierID, r.sname, r.status, 'London')


Inserting a row into the view will now cause a row to be inserted into the underlying base table with City value equal to 'London' instead of the default (and the new row will now appear in the view, presumably as required)."
(C. J. Date, An Introduction to Database Systems: Eighth Edition, Pearson Education, 2004, p278 )