Results 1 to 3 of 3

Thread: Triggers

  1. #1
    Join Date
    Mar 2011
    Location
    Sydney, Australia
    Posts
    58

    Unanswered: Triggers

    I have an entry table:
    event_id int not null references event(event_id),
    horse_id int not null references horse(horse_id),
    place char(2),
    primary key (event_id, horse_id)

    I need to set up a trigger which is activated by a Before Insert event and which generates a message based on situations below:
    When there's a duplication of event_id and place, the message is "Duplicate Event_id xxx Place yyy".
    If the newly inserted event_id and place aren't already in the table, the trigger should produce this message: "No duplication of event_id and place".

    I am having trouble with finding the correct syntax here. I have seen several different expressions of the process, and have been working with two of these. I'm unsure of which way to head with this one and I'd be grateful for any helpful suggestions.

    Attempt 1:

    delimiter //
    create
    trigger entry_tg
    after insert on entry
    for each row
    begin
    declare dummy int;

    if new.event_id = event_id

    then
    select 'Duplicate Event_id' into dummy
    from entry;

    elseif new.event_id <> event_id
    then
    select 'No duplication of event_id and place.' into dummy
    from entry;
    end if;
    end; //
    delimiter ;

    Attempt 2:
    delimiter //
    create
    trigger entry_tg
    before insert on entry
    for each row
    begin
    declare tot_rows integer;

    select count(*)
    into tot_rows
    from entry
    where event_id = new.event_id;

    if tot_rows > 1
    then
    declare message_fail char(50);
    select ''Duplicate Event_id' new.event_id 'and Place' new.place.'
    into message
    from entry

    elseif tot_rows = 1
    then
    declare message_okay char(50);
    select 'No duplication of event_id and place.'
    into message
    from entry
    end if;
    end; //
    delimiter ;

  2. #2
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    880
    Unfortunately in 5.1 raising exceptions was not available within triggers. This is now available in 5.5 via SIGNAL's check this link.

    I would recommend that you create your own API that performs this check prior to inserting. This can either be a stored procedure, function or within your own development programming language.

    Another techniques that I have read about is causing a divide by 0 error within the trigger. If you receive this exception in your application you know that this is related to a specific error. You could then check for the error.
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com
    Follow me on Twitter

  3. #3
    Join Date
    Mar 2011
    Location
    Sydney, Australia
    Posts
    58
    Thanks for the link to the 5.5 solution, Ronan.

Posting Permissions

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