Results 1 to 8 of 8
  1. #1
    Join Date
    Aug 2003
    Location
    Virginia
    Posts
    10

    Unanswered: 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.

  2. #2
    Join Date
    Aug 2003
    Location
    Massachusetts, USA
    Posts
    106

    Re: Creating Triggers

    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.

  3. #3
    Join Date
    Aug 2003
    Location
    Virginia
    Posts
    10

    Re: Creating Triggers

    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.

  4. #4
    Join Date
    Aug 2003
    Location
    Massachusetts, USA
    Posts
    106

    Re: Creating Triggers

    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.

  5. #5
    Join Date
    Aug 2003
    Location
    Virginia
    Posts
    10

    Re: Creating Triggers

    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

  6. #6
    Join Date
    Aug 2003
    Location
    Massachusetts, USA
    Posts
    106

    Re: Creating Triggers

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

  7. #7
    Join Date
    Aug 2003
    Location
    Virginia
    Posts
    10

    Re: Creating Triggers

    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??

  8. #8
    Join Date
    Aug 2003
    Location
    Massachusetts, USA
    Posts
    106

    Re: Creating Triggers

    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??

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •