Question

sql 2000 触发器 ； 当我在表中插入数据时，触发另一个表更新

下面是我写的。。。但是不对。。求解
CREATE TRIGGER In_Filling
ON BG_Filling
FOR INSERT AS
DECLARE @S_fno INT,@S_eno INT, @S_ceo INT
SELECT @S_ceo = f_FEflag FROM INSERTED
SELECT @S_fno= t_count FROM BG_Trigger WHERE t_id = '1'
SELECT @S_eno= t_count FROM BG_Trigger WHERE t_id = '2'
IF (@S_ceo = '0')
BEGIN
ROLLBACK TRANSACTION
@S_fno = @S_fno+1
UPDATE BG_Trigger SET t_count = @S_fno WHERE t_id = '1'
END

IF (@S_ceo = '1')
BEGIN
ROLLBACK TRANSACTION
@S_eno = @S_eno+1
UPDATE BG_Trigger SET t_count = @S_eno WHERE t_id = '2'
END
自己解决了。但还是谢谢各位，给第一位了。。
CREATE TRIGGER [Ins_Filling] ON [dbo].[BG_Filling]
FOR INSERT
AS
DECLARE @S_fno INT,@S_eno INT, @S_ceo INT
SELECT @S_ceo = f_FEflag FROM INSERTED
if (@S_ceo = '0')
BEGIN
UPDATE BG_Trigger SET t_count = t_count + 1 WHERE t_id = '1'
END
if (@S_ceo = '1')
BEGIN
UPDATE BG_Trigger SET t_count = t_count + 1 WHERE t_id = '2'
END

Answer 1

都没定义事务，为何要回滚事务.
--ROLLBACK TRANSACTION
--如下试试：
CREATE TRIGGER In_Filling
ON BG_Filling
FOR INSERT AS
DECLARE @S_fno INT,@S_eno INT, @S_ceo INT
SELECT @S_ceo = f_FEflag FROM INSERTED
SELECT @S_fno= t_count FROM BG_Trigger WHERE t_id = '1'
SELECT @S_eno= t_count FROM BG_Trigger WHERE t_id = '2'
IF (@S_ceo = '0')
BEGIN
SET @S_fno = @S_fno+1
UPDATE BG_Trigger SET t_count = @S_fno WHERE t_id = '1'
END
IF (@S_ceo = '1')
BEGIN
SET @S_eno = @S_eno+1
UPDATE BG_Trigger SET t_count = @S_eno WHERE t_id = '2'
END

Answer 2

CREATE TRIGGER In_Filling
ON BG_Filling
FOR INSERT AS
DECLARE @S_fno INT,@S_eno INT, @S_ceo INT
set @S_ceo = (select f_FEflag from inserted)
set @S_fno= (select t_count FROM BG_Trigger WHERE t_id = '1')
set @S_eno= (select t_count FROM BG_Trigger WHERE t_id = '2')

UPDATE BG_Trigger SET t_count = (case when t_id='1' and 0=@S_ceo then @S_fno+1 when 1=@S_ceo and t_id='2' then @S_eno+1 else t_count end)

我看着有点怪怪的，难道你BG_Trigger就两条数据？是用来记录最大值得的？

Answer 3

我没看你的，sql server中，每操作一条记录，都有一个备份，比如，你插入一条数据，那么，在临时表中就有一条记录 ，这个就是你刚刚插入的那个数据，你可以在触发器中操作他，从而实现根据这条刚刚操作的数据相关的更新，具体的你查资料吧，表名应该是INSERT_ 开头的