谢谢大神,目前来看,应该是这个样子,目前我修改成了游标处理了,不知道以后会不会出现问题。 但是还是有点疑问: 就是插入这个表的方式用事物来做的,sql语句也是是一条条执行的,难道是不同用户同时执行的事物造成的冲突? 但是如果即便如此,我上边这个触发器中 更新USER_ID 也应该不是原来的userid啊,有些疑惑,大神有空就没分析下了,没有就算了,谢谢下边是我修改的触发器:ALTER TRIGGER [dbo].[TriInertGroupAndPid] ON [dbo].[VISITS] AFTER INSERT AS DECLARE @id NUMERIC(30, 0), @userid NUMERIC(10, 0), @groupid NUMERIC(10, 0), @puserid NUMERIC(10, 0), @personid NUMERIC(10, 0);
DECLARE visits_cursor CURSOR FOR SELECT ID, USER_ID FROM INSERTED;
OPEN visits_cursor
FETCH NEXT FROM visits_cursor INTO @id,@userid WHILE @@FETCH_STATUS = 0 BEGIN SELECT @groupid = GROUP_ID FROM dbo.USERS WHERE ID = @userid; IF (@groupid = 92) BEGIN SELECT @puserid = ISNULL( ( SELECT ID FROM dbo.USERS WHERE PERSON_ID = p.MANAGER_ID ), 0 ) FROM dbo.USERS AS u INNER JOIN dbo.PERSONS p ON u.PERSON_ID = p.ID WHERE u.ID = @userid AND u.GROUP_ID = 92 AND p.DOMAIN_ID = 26; END
IF (@puserid IS NOT NULL) BEGIN --UPDATE v SET U_GROUP_ID= @groupid,P_USER_ID=@puserid FROM dbo.VISITS v,INSERTED i WHERE v.ID=i.ID AND v.USER_ID=i.USER_ID --jxl修改 USER_ID为 用 父ID 代替,P_USER_ID 用 巡店的 USER_ID 代替,person_id 同样用父ID代替 SELECT @personid = PERSON_ID FROM dbo.USERS WHERE ID = @puserid; --U_GROUP_ID 这里直接用90 就是emo的,以后如果有需求可以用查询方式,意义也不大 UPDATE v SET U_GROUP_ID = 90, USER_ID = @puserid, person_id = @personid, P_USER_ID = i.USER_ID FROM dbo.VISITS v, INSERTED i WHERE v.ID = i.ID AND i.ID=@id AND v.CUSTOMER_ID = i.CUSTOMER_ID END ELSE BEGIN UPDATE v SET U_GROUP_ID = @groupid FROM dbo.VISITS v, INSERTED i WHERE v.ID = i.ID AND i.ID=@id AND v.USER_ID = i.USER_ID END FETCH NEXT FROM visits_cursor INTO @id,@userid END
我本以为我写的不错了,原来在大神眼里就是 谢谢了,U_GROUP_ID,P_USER_ID 这几列都是我在数据了加入的新列,所以后边必须更新这列 这是最终的代码,应该可以结贴了 再次感谢 @Tiger_Zhao ,@lzw_0736 ! USE [1101_SFA_V2.2_New] GO /****** Object: Trigger [dbo].[TriInertGroupAndPid] Script Date: 02/10/2015 10:18:46 ******/CREATE TRIGGER [dbo].[TriInertGroupAndPid] ON [dbo].[VISITS] AFTER INSERT AS BEGIN DECLARE @id NUMERIC(30, 0), @userid NUMERIC(10, 0), @groupid NUMERIC(10, 0), @puserid NUMERIC(10, 0), @personid NUMERIC(10, 0);
DECLARE visits_cursor CURSOR FOR SELECT ID, USER_ID FROM INSERTED;
OPEN visits_cursor
FETCH NEXT FROM visits_cursor INTO @id,@userid WHILE @@FETCH_STATUS = 0 BEGIN SELECT @groupid = GROUP_ID FROM dbo.USERS WHERE ID = @userid;
SET @puserid=0; IF (@groupid = 92) BEGIN SELECT @puserid = ISNULL( ( SELECT ID FROM dbo.USERS WHERE PERSON_ID = p.MANAGER_ID ), 0 ) FROM dbo.USERS AS u INNER JOIN dbo.PERSONS p ON u.PERSON_ID = p.ID WHERE u.ID = @userid AND u.GROUP_ID = 92 AND p.DOMAIN_ID = 26; END
IF (@puserid<>0) BEGIN SELECT @personid = PERSON_ID FROM dbo.USERS WHERE ID = @puserid; UPDATE dbo.VISITS SET U_GROUP_ID = 90, USER_ID = @puserid, person_id = @personid, P_USER_ID = USER_ID WHERE ID=@id END ELSE BEGIN UPDATE dbo.VISITS SET U_GROUP_ID = @groupid WHERE ID=@id END
FETCH NEXT FROM visits_cursor INTO @id,@userid END
但是还是有点疑问:
就是插入这个表的方式用事物来做的,sql语句也是是一条条执行的,难道是不同用户同时执行的事物造成的冲突?
但是如果即便如此,我上边这个触发器中 更新USER_ID 也应该不是原来的userid啊,有些疑惑,大神有空就没分析下了,没有就算了,谢谢下边是我修改的触发器:ALTER TRIGGER [dbo].[TriInertGroupAndPid]
ON [dbo].[VISITS]
AFTER INSERT
AS
DECLARE @id NUMERIC(30, 0),
@userid NUMERIC(10, 0),
@groupid NUMERIC(10, 0),
@puserid NUMERIC(10, 0),
@personid NUMERIC(10, 0);
DECLARE visits_cursor CURSOR
FOR
SELECT ID,
USER_ID
FROM INSERTED;
OPEN visits_cursor
FETCH NEXT FROM visits_cursor
INTO @id,@userid
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @groupid = GROUP_ID
FROM dbo.USERS
WHERE ID = @userid;
IF (@groupid = 92)
BEGIN
SELECT @puserid = ISNULL(
(
SELECT ID
FROM dbo.USERS
WHERE PERSON_ID = p.MANAGER_ID
),
0
)
FROM dbo.USERS AS u
INNER JOIN dbo.PERSONS p
ON u.PERSON_ID = p.ID
WHERE u.ID = @userid
AND u.GROUP_ID = 92
AND p.DOMAIN_ID = 26;
END
IF (@puserid IS NOT NULL)
BEGIN
--UPDATE v SET U_GROUP_ID= @groupid,P_USER_ID=@puserid FROM dbo.VISITS v,INSERTED i WHERE v.ID=i.ID AND v.USER_ID=i.USER_ID
--jxl修改 USER_ID为 用 父ID 代替,P_USER_ID 用 巡店的 USER_ID 代替,person_id 同样用父ID代替
SELECT @personid = PERSON_ID
FROM dbo.USERS
WHERE ID = @puserid;
--U_GROUP_ID 这里直接用90 就是emo的,以后如果有需求可以用查询方式,意义也不大
UPDATE v
SET U_GROUP_ID = 90,
USER_ID = @puserid,
person_id = @personid,
P_USER_ID = i.USER_ID
FROM dbo.VISITS v,
INSERTED i
WHERE v.ID = i.ID AND i.ID=@id
AND v.CUSTOMER_ID = i.CUSTOMER_ID
END
ELSE
BEGIN
UPDATE v
SET U_GROUP_ID = @groupid
FROM dbo.VISITS v,
INSERTED i
WHERE v.ID = i.ID AND i.ID=@id
AND v.USER_ID = i.USER_ID
END
FETCH NEXT FROM visits_cursor INTO @id,@userid
END
CLOSE visits_cursor
DEALLOCATE visits_cursor
插入的时候用了事务的,在c#代码里边用了command.Transaction 这种方式做的,
inserted 表数据难道在使用过程中也会受到新插入表的影响吗,那我用了游标那是否也没用呢,
太菜,还请解释下。
还有INSERTED是已经插入了VISITS的数据,后一句UPDATE再原样更新VISITS多此一举。
游标循环中@puserid没有清空,@groupid <> 92 的情况下是等同于上一条的数据。
谢谢了,U_GROUP_ID,P_USER_ID 这几列都是我在数据了加入的新列,所以后边必须更新这列
这是最终的代码,应该可以结贴了
再次感谢 @Tiger_Zhao ,@lzw_0736 !
USE [1101_SFA_V2.2_New]
GO
/****** Object: Trigger [dbo].[TriInertGroupAndPid] Script Date: 02/10/2015 10:18:46 ******/CREATE TRIGGER [dbo].[TriInertGroupAndPid]
ON [dbo].[VISITS]
AFTER INSERT
AS
BEGIN
DECLARE @id NUMERIC(30, 0),
@userid NUMERIC(10, 0),
@groupid NUMERIC(10, 0),
@puserid NUMERIC(10, 0),
@personid NUMERIC(10, 0);
DECLARE visits_cursor CURSOR
FOR
SELECT ID,
USER_ID
FROM INSERTED;
OPEN visits_cursor
FETCH NEXT FROM visits_cursor
INTO @id,@userid
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @groupid = GROUP_ID
FROM dbo.USERS
WHERE ID = @userid;
SET @puserid=0;
IF (@groupid = 92)
BEGIN
SELECT @puserid = ISNULL(
(
SELECT ID
FROM dbo.USERS
WHERE PERSON_ID = p.MANAGER_ID
),
0
)
FROM dbo.USERS AS u
INNER JOIN dbo.PERSONS p
ON u.PERSON_ID = p.ID
WHERE u.ID = @userid
AND u.GROUP_ID = 92
AND p.DOMAIN_ID = 26;
END
IF (@puserid<>0)
BEGIN
SELECT @personid = PERSON_ID
FROM dbo.USERS
WHERE ID = @puserid;
UPDATE dbo.VISITS
SET U_GROUP_ID = 90,
USER_ID = @puserid,
person_id = @personid,
P_USER_ID = USER_ID
WHERE ID=@id
END
ELSE
BEGIN
UPDATE dbo.VISITS
SET U_GROUP_ID = @groupid
WHERE ID=@id
END
FETCH NEXT FROM visits_cursor INTO @id,@userid
END
CLOSE visits_cursor
DEALLOCATE visits_cursor
END