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 <anan...@gmail.com> 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.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 >>>>>> >>>>> >>>>> >>>> >>> >> >