34,576
社区成员
发帖
与我相关
我的任务
分享
select workin from inserted
CREATE TABLE branch([number] INT,[count] INT)
INSERT branch
SELECT 1,0 UNION ALL
SELECT 2,0
TRUNCATE TABLE branch
CREATE TABLE employee([name] NVARCHAR(10),workin INT)
DROP TRIGGER t_tmp
CREATE TRIGGER t_tmp ON employee AFTER INSERT,DELETE
AS
UPDATE branch SET [count]=c.sl FROM (SELECT workin,sl=COUNT(1) FROM employee a,branch b WHERE a.workin=b.number GROUP BY workin) c,branch d WHERE c.workin=d.number
GO
INSERT employee VALUES('cc',1)
INSERT employee VALUES('dd',1)
INSERT employee VALUES('eee',1)
INSERT employee VALUES('aa',1)
INSERT employee VALUES('fff',2)
INSERT employee VALUES('aaa',2)
INSERT employee VALUES('aaa',2)
DELETE FROM employee WHERE [name]='cc'
DELETE FROM employee WHERE [name]='fff'
SELECT * FROM branch
SELECT * FROM employee
create trigger trg_branch on employee after update
as
declare @add_name int
declare @delete_name int
declare @count_name int
set @add_name =0
set @delete_name =0
set @count_name =0
declare @workin varchar(100)
if exists(select 1 from inserted)
begin
select @add_name =count(1) from inserted
select @workin =workin from inserted
end
if exists(select 1 from deleted)
begin
select @delete_name =count(1) from deleted
select @workin =workin from deleted
end
set @count_name=@add_name -@delete_name
update branch set count=count+@count_name where number = @workin
update branch set count=count+1 where number=inserted.workin
update branch set count=count-1 where number=deleted.workin
select workin from inserted
select workin from deleted