ALTERTRIGGER[UserUpdate]ON[dbo].[tblUser]AFTERUPDATEAS--@oldRoleId表示Deleted表的部门ID,@newRoleId表示Inserted表的部门IDDECLARE@oldRoleIdint,@newRoleIdint--@oldUserId表示...
ALTER TRIGGER [UserUpdate]
ON [dbo].[tblUser]
AFTER UPDATE
AS
--@oldRoleId表示Deleted表的部门ID,@newRoleId表示Inserted表的部门ID
DECLARE @oldRoleId int ,@newRoleId int
--@oldUserId表示Deleted表的用户ID,@newUserId表示Inserted表的用户ID
DECLARE @oldUserId int ,@newUserId int
--给以上变量赋值
SELECT @oldRoleId=intRole,@oldUserId=intUserID FROM DELETED
SELECT @newRoleId=intRole,@newUserId=intUserID FROM INSERTED
--如果修改用户信息前跟修改用户信息后的部门ID不一样,则表明确实修改了此用户的部门ID
IF (@oldRoleId!=@newRoleId)
BEGIN
--SELECT intUserAndProgramID,intUserID,intProgramID
--FROM dbo.UserAndProgram
--WHERE intUserID=@newUserId
--先删除之前拥有的权限
DELETE FROM dbo.UserAndProgram WHERE intUserID=@newUserId
--再重新给该用户给新部门的权限
INSERT INTO dbo.UserAndProgram(intUserID,intProgramID)
SELECT @newUserId,intProgramID FROM dbo.tblRoleAndProgram WHERE role_id=@newRoleId
END
以上SQL是SQL server的语法 编写的一个触发器
用MySQL的语法该怎么写?
我怎么写 都错 - -
展开