I took the following lines of code slightly modified and it returned some
data using a normal Query Editor
CREATE TEMPORARY TABLE tempHotelRateAvailability(AuditTrailId
varchar(36),UserId bigint(20),ActionType varchar(36),TableName
varchar(36),RowKey varchar(255),FieldName varchar(36),OldValue
varchar(255),NewValue varchar(255), LoggedOn TIMESTAMP);
INSERT INTO tempHotelRateAvailability VALUES
(UUID(),1,'UPDATE','HotelRateAvailability', 1,'RoomsToSell',1,2, NOW());
INSERT INTO tempHotelRateAvailability VALUES
(UUID(),1,'UPDATE','HotelRateAvailability', 1,'RoomsToSell',4,4, NOW());
SELECT * FROM tempHotelRateAvailability;
On Wed, May 29, 2013 at 2:57 PM, Ananda Kumar <anan...@gmail.com> wrote:
> did u check if data is getting inserted into tempHotelRateAvailability
>
>
> On Wed, May 29, 2013 at 7:21 PM, Neil Tompkins <
> neil.tompk...@googlemail.com> wrote:
>
>> 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 <anan...@gmail.com> 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 <
>>> neil.tompk...@googlemail.com> 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
>>>>
>>>
>>>
>>
>
