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());
On Wed, May 29, 2013 at 2:49 PM, Ananda Kumar <[email protected]> wrote:
> can you please share the code of the trigger. Any kind of error your
> getting
>
>
> On Wed, May 29, 2013 at 6:49 PM, Neil Tompkins <
> [email protected]> wrote:
>
>> Hi,
>>
>> I've a trigger that writes some data to a temporary table; and at the end
>> of the trigger writes all the temporary table data in one insert to our
>> normal Innodb table.
>>
>> However, for some reason the trigger isn't copying the table from the
>> temporary table to the Innodb table. If I write in the trigger the
>> inserts
>> to the Innodb table, it works fine.
>>
>> Any ideas why. I'm running MySQL 5.6.
>>
>> Thanks
>> Neil
>>
>
>
