DECLARE @reports TABLE (empid nchar(5) primary key,
empname nvarchar(50) NOT NULL,
mgrid nchar(5),
title nvarchar(30),
processed tinyint default 0)
-- initialize @Reports with direct reports of the given employee
INSERT @reports
SELECT empid, empname, mgrid, title, 0
FROM employees
WHERE empid = @InEmpId
我的存储过程是这样的
CREATE TRIGGER newEmp ON dbo.employee
FOR INSERT AS /*添加新员工的触发器*/
DECLARE @depID int /*depID: 部门编号*/
SET @depID= (SELECT depID FROM inserted )
SET NOCOUNT ON
IF @depID <>0 /*如果新员工已经被指定了一个部门,那么将该部门默认权限指定给他*/
INSERT INTO empRights /*empRights: 员工权限表*/
SELECT @@identity, rID, needCensor, rights, censorDepID, censorEmpID, 0
FROM depRights WHERE depRights.depID = @depID
SET NOCOUNT OFF
--must insert one by one (除了这句,所有的注释都是现在加的)
我记得我的问题来自SET @depID= (SELECT depID FROM inserted ),因为同时提交多条记录后,即使对于不同的depID,SET @depID=select...这条语句, 只给出select结果的第一个值。
我看了你写的两个存储过程后, 第一个:
update @ckcl set clh=(select 材料编号 from inserted)
同我上面这个有相似地方。
第二个,用cursor:
for select 仓库编号 from tbl仓库 select @clh=材料编号 from inserted