>>>> 2013/05/29 14:51 +0100, Neil Tompkins >>>>
This is my Trigger which doesn't seem to work; but doesn't cause a error
DROP TEMPORARY TABLE IF EXISTS tempHotelRateAvailability;
CREATE TEMPORARY TABLE tempHotelRateAvailability(AuditTrailId
varchar(36),UserId bigint(20),ActionType
enum('INSERT','UPDATE','DELETE'),TableName varchar(36),RowKey
varchar(255),FieldName varchar(36),OldValue text,NewValue text);
IF NEW.RoomsToSell <> OLD.RoomsToSell THEN
INSERT INTO tempHotelRateAvailability VALUES
(UUID(),NEW.LastChangedBy,'UPDATE','HotelRateAvailability',
CONCAT(OLD.RoomID,'|',OLD.Day),'RoomsToSell',OLD.RoomsToSell,NEW.RoomsToSell);
END IF;
IF SELECT COUNT(*) FROM tempHotelRateAvailability > 0 THEN
INSERT INTO AuditTrail
SELECT tempHotelRateAvailability.* FROM tempHotelRateAvailability;
END IF;
DROP TEMPORARY TABLE tempHotelRateAvailability;
However if I use this call in the Trigger and change a value in the table
it works fine;
INSERT INTO AuditTrail
(AuditTrailId,UserId,ActionType,TableName,RowKey,FieldName,OldValue,NewValue,
LoggedOn)
VALUES (UUID(),1,'UPDATE','HotelRateAvailability', 1,'RoomsToSell',1,2,
NOW());
<<<<<<<<
You have left out the opening line, but it looks like AFTER UPDATE; is the
table "AuditTrail", or another?
How did this pass the parser,
IF SELECT COUNT(*) FROM tempHotelRateAvailability > 0 THEN
? If Workbench corrected it, there is no knowing what the code really is. This
is correct,
IF (SELECT COUNT(*) FROM tempHotelRateAvailability) > 0 THEN
but it is just as well to write
IF EXISTS(SELECT * FROM tempHotelRateAvailability) THEN
.
Why bother with the temporary table? It never has more rows; it is just as well
to insert straight into AuditTrail if NEW.RoomsToSell <> OLD.RoomsToSell.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql
