List:General Discussion« Previous MessageNext Message »
From:Ananda Kumar Date:May 29 2013 1:57pm
Subject:Re: Temporary Tables with Triggers Problem
View as plain text  
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