Creating Triggers

Can someone please help me with the following problem:

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

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.Based on my understanding of the below, I sense that an INSTEAD OF trigger isn't appropriate. Are you always overwriting student_id and registration_date with those values? If so, then why not a BEFORE INSERT trigger (if it is Oracle or DB2) on guidry_l table which sets the fields then completes the insert?

If this is off-track, more information will be necessary.

Originally posted by Byrd24
Can someone please help me with the following problem:

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

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.|||Originally posted by dmmac
Based on my understanding of the below, I sense that an INSTEAD OF trigger isn't appropriate. Are you always overwriting student_id and registration_date with those values? If so, then why not a BEFORE INSERT trigger (if it is Oracle or DB2) on guidry_l table which sets the fields then completes the insert?

If this is off-track, more information will be necessary.

I have no idea if I am always overwriting student_id. All I know is that my professor wants us to create an INSTEAD OF trigger using the view that I had created from table guidry_l :

SQL> create table guidry_l (
2 student_id number(8) not null,
3 f_name varchar2(25),
4 l_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)
10 );
And update the student w/ id of 1000.|||Re-read your first post and I see what the update is about.
Ok, then here is a template (it is based on DB2, I see an 'OR REPLACE' in your view example, so if you are using Oracle you will need to figure the correct trigger structure syntax -- should be that different):

CREATE TRIGGER x INSTEAD OF UPDATE ON lguidry_view
REFERENCING NEW AS N OLD AS O
FOR EACH ROW
BEGIN
IF (n.student_id = 1000) THEN
UPDATE guidryl SET registration_date = DATE('8/12/2003')
WHERE student_id = N.student_id
END IF;
END

Originally posted by Byrd24
I have no idea if I am always overwriting student_id. All I know is that my professor wants us to create an INSTEAD OF trigger using the view that I had created from table guidry_l :

SQL> create table guidry_l (
2 student_id number(8) not null,
3 f_name varchar2(25),
4 l_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)
10 );
And update the student w/ id of 1000.|||Okay I have tried it and I am still having problems. I'm getting the following error:

Warning: Trigger created with compilation errors.

What am I doing wrong?

Create or replace trigger updateguidry
INSTEAD OF UPDATE ON lguidry_view
REFERENCING NEW AS N OLD AS O
FOR EACH ROW
BEGIN
IF (n.student_id = 1000) THEN
UPDATE guidryl SET registration_date = DATE('8/12/2003')
WHERE student_id = N.student_id
END IF;
end updateguidry;
/

Originally posted by dmmac
Re-read your first post and I see what the update is about.
Ok, then here is a template (it is based on DB2, I see an 'OR REPLACE' in your view example, so if you are using Oracle you will need to figure the correct trigger structure syntax -- should be that different):

CREATE TRIGGER x INSTEAD OF UPDATE ON lguidry_view
REFERENCING NEW AS N OLD AS O
FOR EACH ROW
BEGIN
IF (n.student_id = 1000) THEN
UPDATE guidryl SET registration_date = DATE('8/12/2003')
WHERE student_id = N.student_id
END IF;
END|||Try putting a semi-colon at the end of the UPDATE statement.

Originally posted by Byrd24
Okay I have tried it and I am still having problems. I'm getting the following error:

Warning: Trigger created with compilation errors.

What am I doing wrong?

Create or replace trigger updateguidry
INSTEAD OF UPDATE ON lguidry_view
REFERENCING NEW AS N OLD AS O
FOR EACH ROW
BEGIN
IF (n.student_id = 1000) THEN
UPDATE guidryl SET registration_date = DATE('8/12/2003')
WHERE student_id = N.student_id
END IF;
end updateguidry;
/|||Okay I create my trigger a little bit differently:

SQL> CREATE OR REPLACE TRIGGER updateguidry
2 INSTEAD OF UPDATE ON lguidry_view
3 FOR EACH ROW
4 BEGIN
5 IF (:new.student_id = '1000') THEN
6 UPDATE guidry_l
7 SET registration_date = TO_DATE('12-AUG-2003','DD-MON-YYYY')
8 WHERE student_id = :new.student_id;
9 END IF;
10 END updateguidry;
11 /

Trigger created.

I finally created the trigger so now how do I get it to work?? From my understanding, I am suppose to create an update code for it to get it to work. I tried to update the info using the lguidry_view and I got an error message:

ERROR at line 1:
ORA-04098: trigger 'CIS305AD8.LGUIDRY_VIEW' is invalid and failed re-validation

I'm guessing I did the trigger right??|||Change if to IF(:new.student_id = 1000) THEN

Originally posted by Byrd24
Okay I create my trigger a little bit differently:

SQL> CREATE OR REPLACE TRIGGER updateguidry
2 INSTEAD OF UPDATE ON lguidry_view
3 FOR EACH ROW
4 BEGIN
5 IF (:new.student_id = '1000') THEN
6 UPDATE guidry_l
7 SET registration_date = TO_DATE('12-AUG-2003','DD-MON-YYYY')
8 WHERE student_id = :new.student_id;
9 END IF;
10 END updateguidry;
11 /

Trigger created.

I finally created the trigger so now how do I get it to work?? From my understanding, I am suppose to create an update code for it to get it to work. I tried to update the info using the lguidry_view and I got an error message:

ERROR at line 1:
ORA-04098: trigger 'CIS305AD8.LGUIDRY_VIEW' is invalid and failed re-validation

I'm guessing I did the trigger right??