Description:
hi,
I try to delete a trigger, database is created from dumpsql export, in a newly installed 5.018 on macosx 10.4.5
I have been using Triggers now for a few weeks. So not saying I'm expert. I use the Terminal to create and delete triggers.
After few times doing drop and create suddenly drop doesn't work any more and create says Trigger exist already.
DROP TRIGGER trig_t_attribute_before_update2;
This gives error:
ERROR 1146 (42S02): Table 'tvplanner5.T_ATTRIBUTE_VALUE' doesn't exist
DELIMITER $$
CREATE TRIGGER trig_t_attribute_before_update2 BEFORE UPDATE ON T_ATTRIBUTE_VALUE
FOR EACH ROW
BEGIN
DECLARE aCity CHAR(80);
DECLARE aMediaName CHAR(80);
DECLARE aMediaType INT(11);
DECLARE aMediaDuration FLOAT;
IF new.ATV_VALUE_DATE IS NOT NULL THEN
SET NEW.ATV_VALUE_STRING = tvplanner_DateToString(new.ATV_VALUE_DATE);
ELSEIF new.ATV_VALUE_TIME IS NOT NULL THEN
SET NEW.ATV_VALUE_STRING = new.ATV_VALUE_TIME;
ELSEIF (new.ATV_VALUE_LNK_LOCATION IS NOT NULL) and ((old.ATV_VALUE_LNK_LOCATION IS NULL) or (old.ATV_VALUE_LNK_LOCATION <> new.ATV_VALUE_LNK_LOCATION)) THEN
SELECT NAM_NAME INTO aCity FROM L_LOCATION, T_L_NAME WHERE (LOC_INDEX = new.ATV_VALUE_LNK_LOCATION) and (LOC_LNK_CITY_NAME = NAM_ID) and (NAM_LNK_LANGUAGE = @varUserLanguage);
SET NEW.ATV_VALUE_STRING = aCity;
ELSEIF (new.ATV_VALUE_LNK_MEDIA IS NOT NULL) and ((old.ATV_VALUE_LNK_MEDIA IS NULL) or (old.ATV_VALUE_LNK_MEDIA <> new.ATV_VALUE_LNK_MEDIA)) THEN
SELECT MED_NAME,MED_LNK_MEDIA_TYPE,MED_DURATION INTO aMediaName,aMediaType,aMediaDuration FROM T_MEDIA WHERE (MED_INDEX = new.ATV_VALUE_LNK_MEDIA);
IF (aMediaType = 1) OR (aMediaType = 2) then
SET NEW.ATV_VALUE_STRING = CONCAT(aMediaName,' (',aMediaDuration,'s)');
ELSE
SET NEW.ATV_VALUE_STRING = aMediaName;
END IF;
END IF;
END$$
This gives error:
ERROR 1359 (HY000): Trigger already exists
DELIMITER ;
When doing Show Triggers; No triggers are shown (currently only have on trigger).
So workarround I use is to change the name of the trigger function.
How to repeat:
I'm not sure how it starts happening.
Description: hi, I try to delete a trigger, database is created from dumpsql export, in a newly installed 5.018 on macosx 10.4.5 I have been using Triggers now for a few weeks. So not saying I'm expert. I use the Terminal to create and delete triggers. After few times doing drop and create suddenly drop doesn't work any more and create says Trigger exist already. DROP TRIGGER trig_t_attribute_before_update2; This gives error: ERROR 1146 (42S02): Table 'tvplanner5.T_ATTRIBUTE_VALUE' doesn't exist DELIMITER $$ CREATE TRIGGER trig_t_attribute_before_update2 BEFORE UPDATE ON T_ATTRIBUTE_VALUE FOR EACH ROW BEGIN DECLARE aCity CHAR(80); DECLARE aMediaName CHAR(80); DECLARE aMediaType INT(11); DECLARE aMediaDuration FLOAT; IF new.ATV_VALUE_DATE IS NOT NULL THEN SET NEW.ATV_VALUE_STRING = tvplanner_DateToString(new.ATV_VALUE_DATE); ELSEIF new.ATV_VALUE_TIME IS NOT NULL THEN SET NEW.ATV_VALUE_STRING = new.ATV_VALUE_TIME; ELSEIF (new.ATV_VALUE_LNK_LOCATION IS NOT NULL) and ((old.ATV_VALUE_LNK_LOCATION IS NULL) or (old.ATV_VALUE_LNK_LOCATION <> new.ATV_VALUE_LNK_LOCATION)) THEN SELECT NAM_NAME INTO aCity FROM L_LOCATION, T_L_NAME WHERE (LOC_INDEX = new.ATV_VALUE_LNK_LOCATION) and (LOC_LNK_CITY_NAME = NAM_ID) and (NAM_LNK_LANGUAGE = @varUserLanguage); SET NEW.ATV_VALUE_STRING = aCity; ELSEIF (new.ATV_VALUE_LNK_MEDIA IS NOT NULL) and ((old.ATV_VALUE_LNK_MEDIA IS NULL) or (old.ATV_VALUE_LNK_MEDIA <> new.ATV_VALUE_LNK_MEDIA)) THEN SELECT MED_NAME,MED_LNK_MEDIA_TYPE,MED_DURATION INTO aMediaName,aMediaType,aMediaDuration FROM T_MEDIA WHERE (MED_INDEX = new.ATV_VALUE_LNK_MEDIA); IF (aMediaType = 1) OR (aMediaType = 2) then SET NEW.ATV_VALUE_STRING = CONCAT(aMediaName,' (',aMediaDuration,'s)'); ELSE SET NEW.ATV_VALUE_STRING = aMediaName; END IF; END IF; END$$ This gives error: ERROR 1359 (HY000): Trigger already exists DELIMITER ; When doing Show Triggers; No triggers are shown (currently only have on trigger). So workarround I use is to change the name of the trigger function. How to repeat: I'm not sure how it starts happening.