Results 1 to 10 of 10

Thread: SQL triggers

  1. #1
    Join Date
    May 2011
    Posts
    5

    Unanswered: SQL triggers

    Hello world,
    I am using ORACLE 10g with TOAD 9.0.1.
    This is the SQL statement is use for creating a trigger using the TOAD SQL editor in my database,

    CREATE TRIGGER INCREMENT_PROPERTY_ID
    AFTER INSERT ON PROPERTY
    REFERENCING NEW AS NEW_TUPLE
    FOR EACH ROW WHEN PROPERTY_ID=-1
    BEGIN
    UPDATE PROPERTY
    SET PROPERTY_ID=PROPERTY_ID_INCREMENTER.nextval
    WHERE PROPERTY_ID=-1
    END INCREMENT_PROPERTY_ID;
    .
    run;

    Is this statement correct according to the syntax?
    The main problem is that this statement does not execute in TOAD, All other statements including the CREATE_SEQUENCE statements execute successfully.

    I can't figure what is the problem here?
    Can anyone help?

  2. #2
    Join Date
    Nov 2003
    Posts
    2,934
    Provided Answers: 12
    As I have already explained in my other answer, you should only assign the sequence value to the column:

    Code:
    CREATE TRIGGER INCREMENT_PROPERTY_ID
    BEFORE INSERT ON PROPERTY
    REFERENCING NEW AS NEW_TUPLE
    FOR EACH ROW WHEN (PROPERTY_ID = -1)
    BEGIN
       :NEW_TUPLE.property_id := property_id_incrementer.nextval;
    END INCREMENT_PROPERTY_ID;
    And if you want to change a column's value in a trigger, you have to use a BEFORE trigger, not an AFTER trigger (once the AFTER trigger is fired, the row is already inserted into the database)

    You cannot use the UPDATE statement to update the row that has been passed to the trigger.

    And please use [code] tags in the future to make your SQL code readable.
    Last edited by shammat; 05-02-11 at 13:10.

  3. #3
    Join Date
    May 2011
    Posts
    5
    Thank you for the reply.

    Can you explain to me that what does the 'REFERENCING AS NEW' and 'REFERENCING AS OLD' means?

    What I am thinking is that, I will create a trigger that will be fired after I have inserted a tuple, the value of the ID that I want to assign will be any arbitrary value, lets say -1.

    Now in the body of trigger, i will write an update statement that will change the value of this ID that has a value of -1, to the 'nextval' of the sequence I have created.

    But, the main problem is that, I just copy pasted your suggested SQL statement into the TOAD SQL Editor, and It still won't execute. I can almost every other statement including the CREATE SEQUENCE statement. But the CREATE TRIGGER statement does not executes. Nothing happens. I have also checked the Database Triggers, its still empty.

    I do not know what is wrong over here?

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  5. #5
    Join Date
    Nov 2003
    Posts
    2,934
    Provided Answers: 12
    Quote Originally Posted by uzipaz View Post
    Can you explain to me that what does the 'REFERENCING AS NEW' and 'REFERENCING AS OLD' means?
    It establishes an alias for the new and old rows (but in an INSERT trigger there are not "old" rows) under which you can access a "record" that contains all columns of the inserted row. If you leave it out, it defaults to "NEW" and "OLD" (see my example code)

    What I am thinking is that, I will create a trigger that will be fired after I have inserted a tuple, the value of the ID that I want to assign will be any arbitrary value, lets say -1.
    The columns in a new row do not have an "arbitrary" value. It's either NULL, the value specified with a DEFAULT clause in the CREATE TABLE or the value you supplied in the INSERT statement.

    Now in the body of trigger, i will write an update statement that will change the value of this ID that has a value of -1, to the 'nextval' of the sequence I have created.
    Again: you do not (actually cannot) UPDATE the table, you assign values to the columns.

    [quote]But, the main problem is that, I just copy pasted your suggested SQL statement into the TOAD SQL Editor, and It still won't execute. I can almost every other statement including the CREATE SEQUENCE statement. But the CREATE TRIGGER statement does not executes. Nothing happens. I have also checked the Database Triggers, its still empty./QUOTE]Check the TOAD manual, you will most probably need to use an "alternate" delimiter. I don't use TOAD so I cannot help you there. In SQL*Plus this works with a / on a single line:

    Code:
    Connected to:
    Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
    With the Partitioning and OLAP options
    
    SQL> create table property (property_id integer, some_data varchar2(10))
      2  /
    
    Table created.
    
    SQL>
    SQL> create sequence property_id_incrementer
      2  /
    
    Sequence created.
    
    SQL>
    SQL> CREATE TRIGGER INCREMENT_PROPERTY_ID
      2  BEFORE INSERT ON PROPERTY
      3  FOR EACH ROW
      4  BEGIN
      5     :NEW.property_id := property_id_incrementer.nextval;
      6  END INCREMENT_PROPERTY_ID;
      7  /
    
    Trigger created.
    
    SQL>
    SQL> insert into property (some_data) values ('foo')
      2  /
    
    1 row created.
    
    SQL>
    SQL> select * from property
      2  /
    
    PROPERTY_ID SOME_DATA
    ----------- ----------
              1 foo
    
    SQL>
    So please consult the TOAD manual to find out how to run a CREATE TRIGGER statement.

  6. #6
    Join Date
    May 2011
    Posts
    5
    Quote Originally Posted by anacedent View Post
    Thanks but I would really appreciate if someone can help me solve my problems rather then giving me links regarding books and webpages. I have checked alot of them and they didn't help me a bit.

  7. #7
    Join Date
    Nov 2003
    Posts
    2,934
    Provided Answers: 12
    Quote Originally Posted by uzipaz View Post
    I have checked alot of them and they didn't help me a bit.
    Well, your questions are so basic, the assumption that you did not read the manual is more than justified.

    How to assign values in a trigger, what the "referencing" clause means and the difference between an before and after trigger are more then well explained in the manual.

    I personally can only answer your questions because I took the time to read the manual

  8. #8
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >Thanks but I would really appreciate if someone can help me solve my problems
    is your problem with Oracle or TOAD?
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  9. #9
    Join Date
    May 2011
    Posts
    5
    The problem is with TOAD I guess, becuase I does not execute the CREATE TRIGGER query (does not even show errors,nothing happens), however, it executes every other statement I write on it.

  10. #10
    Join Date
    Nov 2003
    Posts
    2,934
    Provided Answers: 12
    Quote Originally Posted by uzipaz View Post
    The problem is with TOAD I guess, becuase I does not execute the CREATE TRIGGER query (does not even show errors,nothing happens), however, it executes every other statement I write on it.
    What's wrong with the answer you got here:

    OraFAQ Forum: SQL & PL/SQL Create Trigger not working for autoincrement

Posting Permissions

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