Re: Triggers in batch mode

[Don Beesing]

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.