MySQL Forums
Forum List  »  Triggers

Re: Triggers in batch mode
Posted by: Don Beesing
Date: March 15, 2005 02:26PM

Andrew,

Thanks for the response. My triggers are slightly more complex, but I am following a similiar format, only using BEGIN END syntax. I am working on massive tables, but here is the script:

###start
##Create Trigger schema for faars_cahe NRT
##03/10/05

##changing delimiter to allow mysql to accept begin...end syntax
delimiter //

##C_Aircraft
##Requires addition of 2 columns - platform_med and platform_short
##platform_med example= A_OAV_10_1 platform_short example=OAV10
CREATE TRIGGER AIR1 BEFORE INSERT ON C_Aircraft
FOR EACH ROW
BEGIN
SET NEW.platform_med=SUBSTRING(NEW.entityName,(LOCATE('--',NEW.entityName)+2));
SET NEW.platform_short=
SUBSTRING(NEW.platform_med,LOCATE('AV',NEW.platform_med)-1,LOCATE('_',NEW.platform_med,LOCATE('AV',NEW.platform_med)-1)-LOCATE('AV',NEW.platform_med)+1);
END//

##I_Contact Report
##Requires addition of 2 columns - platform_med and platform_short
##platform_med example= A_OAV_10_1 platform_short example=OAV10
CREATE TRIGGER CR1 BEFORE INSERT ON I_ContactReport
FOR EACH ROW
BEGIN
IF(NEW.platform_id_ID != 'SOAR') THEN
SET NEW.platform_med=SUBSTRING(NEW.platform_id_ID,(LOCATE('--',NEW.platform_id_ID)+2));
IF (LOCATE('AV', NEW.platform_id_ID)) THEN
SET NEW.platform_short =
SUBSTRING(NEW.platform_med,LOCATE('AV',NEW.platform_med)-1,LOCATE('_',NEW.platform_med,LOCATE('AV',NEW.platform_med)-1)-LOCATE('AV',NEW.platform_med)+1);
ELSEIF(LOCATE('GS', NEW.platform_id_ID)) THEN
SET NEW.platform_short= SUBSTRING(NEW.platform_med,LOCATE('GS',NEW.platform_med)-1,LOCATE('_',NEW.platform_med,LOCATE('GS',NEW.platform_med)-1)-LOCATE('GS',NEW.platform_med)+1);
END IF;
END IF;
SET NEW.entityType=UPPER(SUBSTRING(NEW.entity_id_ID, 1,2));
END//

##C_Track
##Requires addition of 2 columns - trackType, assoc_entity_type
CREATE TRIGGER TR1 BEFORE INSERT ON C_Track
FOR EACH ROW
BEGIN
SET NEW.trackType=UPPER(SUBSTRING(NEW.entityName,1,2));
SET NEW.assoc_entity_type=UPPER(SUBSTRING(NEW.assoc_entity_id_ID, 1,2));
END//

CREATE TRIGGER HU1 BEFORE INSERT ON C_Human
FOR EACH ROW
BEGIN
SET NEW.entityType=UPPER(SUBSTRING(NEW.entityName, 1,2));
END//

CREATE TRIGGER GV1 BEFORE INSERT ON C_GroundVehicle
FOR EACH ROW
BEGIN
SET NEW.entityType=UPPER(SUBSTRING(NEW.entityName, 1,2));
END//

CREATE TRIGGER AD1 BEFORE INSERT ON C_AirDefense
FOR EACH ROW
BEGIN
SET NEW.entityType=UPPER(SUBSTRING(NEW.entityName, 1,2));
END//

##resetting delimiter so that triggers will actually work in normal mysql mode
delimiter ;
###end


Thanks.

Options: ReplyQuote


Subject
Views
Written By
Posted
3955
March 15, 2005 09:59AM
2530
March 15, 2005 01:03PM
Re: Triggers in batch mode
4856
March 15, 2005 02:26PM
2509
March 15, 2005 05:06PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.