I have an entry table:
event_id int not null references event(event_id),
horse_id int not null references horse(horse_id),
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.
after insert on entry
for each row
declare dummy int;
if new.event_id = event_id
select 'Duplicate Event_id' into dummy
elseif new.event_id <> event_id
select 'No duplication of event_id and place.' into dummy
before insert on entry
for each row
declare tot_rows integer;
where event_id = new.event_id;
if tot_rows > 1
declare message_fail char(50);
select ''Duplicate Event_id' new.event_id 'and Place' new.place.'
elseif tot_rows = 1
declare message_okay char(50);
select 'No duplication of event_id and place.'
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.