谁会用 MySQL 5.0 的 Trigger ？

我尝试用 Mysql 5.0 的 Trigger，  但牵涉到其它Table的Query好像就行不通了：
create trigger R2BS
after update on registeredUser
for each row
begin

If new.activation <> 0 then

Insert into Seller values(new.UserID,default,default,default);
Insert into Bidder values(new.UserID,default,default,default);

end if;

用这种 insert into database.table values 的方式也是不行

后来用显示现时日期的方法来测试，却行得通阿，Trigger 有 fired 到。。。。

create trigger R2BS
after update on registeredUser
for each row
begin

If new.activation <> 0 then

select current_date；

end if;

唉。。。这到底是由于新版本的技术不成熟，还是我的方法有误呢？

新版本的手册在Trigger上说的很少，大家能有什么Idea吗？

[ Last edited by 梟龍滅天 on 27-6-2005 at 10:51 PM ]

jasonmun

哇,已经有Trigger了?
几时还有 store procedure 呢?

max5007

我想MYSQL还需要时间来改进＾＾

解决了，要用这样的Query才行，唉。

SET @@AUTOCOMMIT=0;
START TRANSACTION;
LOCK TABLES registeredUser WRITE, Seller WRITE, Buyer WRITE, mysql.proc READ;

UPDATE registeredUser SET activation = 1 WHERE userID='test';

UNLOCK TABLES;
COMMIT;

又发现另个问题了，如果Trigger有牵涉到另个有已经有Trigger的Table，就失灵了，大家可以尝试在MySql 5.0以上 运行看看这个Script，只要把其中一个/*。。。*/删掉，原来的Trigger就跑不了了。

*****************************
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;

*****************************

hui_wooi

jasonmun 于 28-6-2005 09:37 AM  说 :
哇,已经有Trigger了?
几时还有 store procedure 呢?

好像已经可以store procedure了。只是version 5还是有很多bug。本身也不能install version 5。