34,593
社区成员
发帖
与我相关
我的任务
分享
declare @RoleCode nvarchar(20),@WRPageType nvarchar(20),@WRPageDescs nvarchar(20)
select @RoleCode='10'
delete from TDOA_WRRolePageType where RoleCode=@RoleCode
select @RoleCode='10'
insert into dbo.TDOA_WRRolePageType(RoleCode,WRPageType,SortNo)
select @RoleCode,WRPageType,SortNo from TDOA_WRPage where ','+@WRPageType+',' like '%,'+WRPageType+',%'
SELECT @RoleCode='10'
set @WRPageDescs=STUFF((select ','+RTRIM(WRPageType) from TDOA_WRRolePageType where RoleCode=@RoleCode ORDER BY SortNo for xml path('')),1,1,'')
SELECT @WRPageDescs
update dbo.TDOA_WRRole set WRPageDescs=@WRPageDescs where RoleCode=@RoleCode
CREATE TRIGGER tr_TDOA_WRRolePageType_insert ON TDOA_WRRolePageType
FOR UPDATE,INSERT,DELETE
AS
BEGIN
UPDATE b
SET [WRPageDescs]=a.[WRPageDescs]
FROM
(SELECT RoleCode,[WRPageDescs]=STUFF((SELECT ','+RTRIM(WRPageType) FROM TDOA_WRRolePageType WHERE RoleCode=i.RoleCode ORDER BY SortNo FOR XML PATH('')) ,1,1,'')
FROM INSERTED AS i
GROUP BY RoleCode)a
INNER JOIN TDOA_WRRole AS b ON a.RoleCode=b.RoleCode
UPDATE b
SET [WRPageDescs]=a.[WRPageDescs]
FROM
(SELECT RoleCode,[WRPageDescs]=STUFF((SELECT ','+RTRIM(WRPageType) FROM TDOA_WRRolePageType WHERE RoleCode=d.RoleCode ORDER BY SortNo FOR XML PATH('')) ,1,1,'')
FROM deleted AS d
GROUP BY RoleCode)a
INNER JOIN TDOA_WRRole AS b ON a.RoleCode=b.RoleCode
END