List:General Discussion« Previous MessageNext Message »
From:Neil Tompkins Date:May 29 2013 2:08pm
Subject:Re: Temporary Tables with Triggers Problem
View as plain text  
OK, the data is going into the temp table.  But when I run the
command INSERT INTO AuditTrail SELECT tempHotelRateAvailability.* FROM
tempHotelRateAvailability;   from the TRIGGER it does not copy the data.

However if I run this query INSERT INTO AuditTrail SELECT
tempHotelRateAvailability.* FROM tempHotelRateAvailability;   from the
MySQL query editor (not the trigger), it copies the data find.

Any ideas ?


On Wed, May 29, 2013 at 3:02 PM, Ananda Kumar <anandkl@stripped> wrote:

> 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.tompkins@stripped> 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 <anandkl@stripped> wrote:
>>
>>> did u check if data is getting inserted into tempHotelRateAvailability
>>>
>>>
>>> On Wed, May 29, 2013 at 7:21 PM, Neil Tompkins <
>>> neil.tompkins@stripped> 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
> <anandkl@stripped>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.tompkins@stripped> 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
>>>>>>
>>>>>
>>>>>
>>>>
>>>
>>
>

Thread
Temporary Tables with Triggers ProblemNeil Tompkins29 May
  • Re: Temporary Tables with Triggers ProblemAnanda Kumar29 May
    • Re: Temporary Tables with Triggers ProblemNeil Tompkins29 May
      • Re: Temporary Tables with Triggers ProblemAnanda Kumar29 May
        • Re: Temporary Tables with Triggers ProblemNeil Tompkins29 May
          • Re: Temporary Tables with Triggers ProblemAnanda Kumar29 May
            • Re: Temporary Tables with Triggers ProblemNeil Tompkins29 May
      • Re: Temporary Tables with Triggers Problemhsv29 May