Quick newbie trigger question
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: Quick newbie trigger question

  1. #1
    Join Date
    Nov 2000
    Posts
    3
    I'm new to using triggers and I'm having a tough time with a basic one. I'm just trying to write a BEFORE INSERT trigger that will evaluate a value in the insert statement, and only actually do the insert if it is a particular value. Otherwise, do not do the insert.

    Lets say the table looks like this:

    USERNAME VARCHAR(255)
    OPERATION VARCHAR(255)

    And I only want the INSERT to execute if the OPERATION value = "save".

    So basically, how would this trigger be written, and specifically how do you refer to the values in the INSERT statement and how would you tell it to do nothing if it fails the evaluation.

    Thanks in advance,
    - David

  2. #2
    Join Date
    Sep 2000
    Posts
    384
    use the alter table command to use the check option.

    you do not need a trigger here .you can very well use the check command
    Radhakrishnan.M

  3. #3
    Join Date
    Nov 2000
    Posts
    3
    Thanks, I'll give that a shot.

    What I also need (and was hoping to get answered with the first question :) ) is a trigger to parse one of the fields before it is inserted into the table.

    So, how do I refer to the values coming in from the INSERT statement? And, once I've manipulated that value, how do I execute the INSERT with the new value?

    Thanks alot!

  4. #4
    Join Date
    Sep 2000
    Posts
    384
    create a partition table with partition column as operation
    1.)save and the next as maxvalue.

    here you need not create a check or trigger.

    if the value is save it will fill in partition first

    otherwise it will be insert into the next partition.

    simple and easy.
    Radhakrishnan.M

  5. #5
    Join Date
    Nov 2000
    Posts
    3
    Sorry, I'm not following you there.

    What I was thinking was something like this. An INSERT comes in that looks like:

    INSERT INTO tablename VALUES ('name=david&X=15','GET');

    And I could use a trigger that would parse that first value to only get one portion of it (i.e. just the 'david' part). The INSERT I would want after the trigger is:

    INSERT INTO tablename VALUES ('david','GET');

    Now, how do I say: Use all the original values from the initial INSERT, but use the new parsed value that I calculated (assume no difficulty in parsing the correct value)?

    Or is a trigger just the wrong way to accomplish this?

    Thanks.

  6. #6
    Join Date
    Jul 2000
    Posts
    296
    You can reference the original values of the insert with :new.
    If you have a parse function the trigger could look like this:

    CREATE OR REPLACE TRIGGER your_trigger
    BEFORE INSERT ON your_table
    FOR EACH ROW
    DECLARE
    BEGIN
    :new.username=your_parsefunction(:new.username);
    END;
    /

  7. #7
    Join Date
    Sep 2000
    Posts
    384
    has the problem been solved..
    Radhakrishnan.M

Posting Permissions

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



Click Here to Expand Forum to Full Width