Re: Table access from triggers has been enabled in 5.0-bk

[junjie_1982]

i am in trouble with updating the patch....

well anyway can you please help to test my script with your latest updated patch? thanks a lot if you can help ;)

/***************************** /
create database testtrigger;

use testtrigger;

create table Seller
(
Seller varchar(10) not null,
Reputation integer(1) default 3,
TotalOfPostedItem integer(3) default 0,
TotalFeedback integer(3) default 0,
constraint seller_pkey primary key(Seller)
) type innodb;

create table Item
(
ItemID tinyint(5) not null auto_increment,
ItemName varchar(100) not null,
ItemDesc Varchar(100) not null,
StartPrice float(10,2) not null,
ReservedPrice float(10,2) not null,
Duration time not null,
TotalTimesOfChosen integer(3) not null default 0,
TotalTimesOfWon integer(3) not null default 0,
PostedBy varchar(10) not null,
WhenPosted timestamp(8) default current_timestamp,
constraint Item_pkey primary key(ItemID),
constraint itmseller_fkey foreign key(PostedBy) references Seller(Seller) ON DELETE CASCADE
) type innodb;

create table ItemForGame
(
GameItemID tinyint(5) not null,
GameID tinyint(5) not null,
TotalBids integer(3) default 0
) type innodb;

delimiter //
/*
Create trigger ForSeller
after insert on Item
for each row
begin
update Seller set TotalOfPostedItem = TotalOfPostedItem + 1 where Seller = new.PostedBy;
end//
*/
Create trigger chosen
after insert on itemforgame
for each row
begin
update Item set Item.TotalTimesOfChosen = Item.TotalTimesOfChosen + 1 where itemid = new.gameitemid;
end//


delimiter ;

insert into seller values('a', default, default,default);
insert into seller values('b', default, default,default);
insert into seller values('c', default, default,default);

/*
SET @@AUTOCOMMIT=0;
START TRANSACTION;
LOCK TABLES Seller WRITE, Item write, itemforgame write, mysql.proc READ;
*/
insert into item (ItemName, ItemDesc, StartPrice, ReservedPrice, Duration, TotalTimesOfChosen, TotalTimesOfWon, PostedBy, WhenPosted)values('Item1','aaa', 100.99, 200.99, 30000, default,default,'a', default);
insert into item (ItemName, ItemDesc, StartPrice, ReservedPrice, Duration, TotalTimesOfChosen, TotalTimesOfWon, PostedBy, WhenPosted)values('Item2','aaa', 150.99, 250.99, 40000, default,default,'a', default);
insert into item (ItemName, ItemDesc, StartPrice, ReservedPrice, Duration, TotalTimesOfChosen, TotalTimesOfWon, PostedBy, WhenPosted)values('Item3','aaa', 110.99, 210.99, 50000, default,default,'a', default);
insert into item (ItemName, ItemDesc, StartPrice, ReservedPrice, Duration, TotalTimesOfChosen, TotalTimesOfWon, PostedBy, WhenPosted)values('Item4','aaa', 130.99, 230.99, 20000, default,default,'b', default);
insert into item (ItemName, ItemDesc, StartPrice, ReservedPrice, Duration, TotalTimesOfChosen, TotalTimesOfWon, PostedBy, WhenPosted)values('Item5','aaa', 170.99, 270.99, 10000, default,default,'b', default);
insert into item (ItemName, ItemDesc, StartPrice, ReservedPrice, Duration, TotalTimesOfChosen, TotalTimesOfWon, PostedBy, WhenPosted)values('Item6','aaa', 190.99, 290.99, 13000, default,default,'c', default);
/*
UNLOCK TABLES;
COMMIT;
*/
SET @@AUTOCOMMIT=0;
START TRANSACTION;
LOCK TABLES ItemForGame WRITE, Item write, Seller write, mysql.proc READ;

insert into itemforgame values(1, 1, default);
insert into itemforgame values(2, 1, default);
insert into itemforgame values(3, 1, default);
insert into itemforgame values(1, 2, default);
insert into itemforgame values(4, 2, default);

UNLOCK TABLES;
COMMIT;


select * from seller;

select * from item;

UNLOCK TABLES;
COMMIT;

drop database testtrigger;

/*****************************/



Edited 1 time(s). Last edit at 07/11/2005 01:08AM by junjie_1982.