22,209
社区成员
发帖
与我相关
我的任务
分享
CREATE TRIGGER tr_update_bjjc_sjz
on dbo.TJB
FOR UPDATE,INSERT
AS
BEGIN
DECLARE @oldsjz int,@newsjz int,@jcid int,@bid INT,@id INT
SELECT @id = id FROM inserted;--获取主键值
SELECT @oldsjz = [sjz] from deleted WHERE id = @id--根据主键值获取旧的实际值
SELECT @newsjz = [sjz] FROM inserted WHERE id = @id--根据主键值获取新的实际值
SELECT @jcid=jc_id ,@bid=b_id from dbo.tjb where id = @id;--根据主键值获取jcid 和bid
if @oldsjz > @newsjz --比较旧值和新值,如果旧值是1,新值是0,那么sjz就减少1.
BEGIN
UPDATE dbo.BJJC
SET sjz = sjz -1
WHERE jc_id =@jcid and b_id=@bid
END
if isnull(@oldsjz,0) < @newsjz --比较旧值和新值,如果旧值是0,或者旧值是null, 新值是1.那么就 加1
BEGIN
UPDATE dbo.BJJC
set sjz = sjz +1
WHERE jc_id = @jcid and b_id = @bid
END
END
CREATE TRIGGER tr_update_bjjc_sjz
on dbo.TJB
FOR UPDATE,INSERT
AS
BEGIN
DECLARE @oldsjz int,@newsjz int,@jcid int,@bid INT,@id INT
DECLARE @t TABLE (rowNum INT IDENTITY(1,1) PRIMARY KEY,id INT)
DECLARE @i INT,@imax INT
INSERT INTO @t(id)
SELECT id FROM INSERTED
SELECT @i=1,@imax=MAX(rowNum) FROM @t
WHILE @i<=@imax
BEGIN
SELECT @id = id FROM @t WHERE rowNum=@i
SELECT @oldsjz = [sjz] from deleted WHERE id = @id--根据主键值获取旧的实际值
SELECT @newsjz = [sjz] FROM inserted WHERE id = @id--根据主键值获取新的实际值
SELECT @jcid=jc_id ,@bid=b_id from dbo.tjb where id = @id;--根据主键值获取jcid 和bid
if @oldsjz > @newsjz --比较旧值和新值,如果旧值是1,新值是0,那么sjz就减少1.
BEGIN
UPDATE dbo.BJJC
SET sjz = sjz -1
WHERE jc_id =@jcid and b_id=@bid
END
if isnull(@oldsjz,0) < @newsjz --比较旧值和新值,如果旧值是0,或者旧值是null, 新值是1.那么就 加1
BEGIN
UPDATE dbo.BJJC
set sjz = sjz +1
WHERE jc_id = @jcid and b_id = @bid
END
SET @i=@i+1
END
END
inserted 是一个集合而不是单条记录, 多条就只能循环处理, 如果你能批量处理也可以, 总之不能按单条处理。
SELECT @id = id
FROM inserted;--获取主键值
这样获取,就相当于从一个表中获取一条数据了,所以只会处理一次。可以用表关联或者in的形式来实现