But, does it work inside the trigger. If not, then based on the logic,
there will not be any data, and data goes not get inserted from temp table
to innodb table
On Wed, May 29, 2013 at 7:29 PM, Neil Tompkins <neil.tompk...@googlemail.com
> wrote:
> 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
>>>>>
>>>>
>>>>
>>>
>>
>
