Re: Safely Create Triggers

[Devang Shah]

Thanks Bob.

I did try that command and it helps. I am however a bit neurotic about programming style and hence I used a different approach. I use a bit kludgy way to implement this.

I have one script file per table, unless there are unique look up tables used by this table. In this script file:
1. I put the conditional drop trigger command in a stored procedure on the top
2. call the stored procedure
3. drop the stored procedure
4. Create Table
5. Create Triggers

I have some SQL development experience but I am a bit new to MySQL.

Considering the ongoing discussion about DROP TRIGGER IF EXISTS issue, I believe that the new feature will show up soon.

If you wish to preview/borrow/use, here is a sample stored procedure for a specific set of triggers.

DROP PROCEDURE IF EXISTS DropOrganizationsTriggers;
DELIMITER //
CREATE PROCEDURE DropOrganizationsTriggers ( )
BEGIN
IF NOT ( 0 <= ( SELECT COUNT( * )
FROM INFORMATION_SCHEMA.TRIGGERS
WHERE INFORMATION_SCHEMA.TRIGGERS.TRIGGER_NAME = 'OnInsertOrganizations'
AND INFORMATION_SCHEMA.TRIGGERS.TRIGGER_SCHEMA = DATABASE() ) )
THEN
DROP TRIGGER OnInsertOrganizations;
END IF;

IF NOT ( 0 <= ( SELECT COUNT( * )
FROM INFORMATION_SCHEMA.TRIGGERS
WHERE INFORMATION_SCHEMA.TRIGGERS.TRIGGER_NAME = 'OnUpdateOrganizations'
AND INFORMATION_SCHEMA.TRIGGERS.TRIGGER_SCHEMA = DATABASE() ) )
THEN
DROP TRIGGER OnUpdateOrganizations;
END IF;
END;
//

DELIMITER ;
CALL DropOrganizationsTriggers ( );
DROP PROCEDURE IF EXISTS DropOrganizationsTriggers;