Trigger thế hệ để sử dụng 3 thế hệ
CREATE TRIGGER TheHe
ON [dbo].[MEMB_STAT]
FOR UPDATE
AS
BEGIN
DECLARE
@slg_thehe tinyint,
@Acc nvarchar(10),
@TheHe nvarchar(50),
@SV nvarchar (20)

SET @slg_thehe =3


-- Check tai khoan adm_block
IF NOT EXISTS (SELECT memb___id FROM MEMB_INFO WHERE memb___id='adm_block')
BEGIN
INSERT INTO MEMB_INFO (memb___id,memb__pwd,memb_name) VALUES ('adm_block',[dbo].[fn_md5]('132465798','adm_block'),'ADMBLOCK')
END

-- Block tai khoan
UPDATE MEMB_INFO SET bloc_code=1 WHERE memb___id='adm_block' AND bloc_code<>1

-- Tao nhan vat 1
IF NOT EXISTS (SELECT Name FROM Character WHERE Name='NhamSV1' AND AccountID='adm_block')
BEGIN
DELETE Character WHERE Name='NhamSV1' AND AccountID<>'adm_block'
EXEC WZ_CreateCharacter 'adm_block', 'NhamSV1', 0
END
-- Tao nhan vat 2
IF NOT EXISTS (SELECT Name FROM Character WHERE Name='NhamSV2' AND AccountID='adm_block')
BEGIN
DELETE Character WHERE Name='NhamSV2' AND AccountID<>'adm_block'
EXEC WZ_CreateCharacter 'adm_block', 'NhamSV2', 32
END
-- Tao nhan vat 3
IF NOT EXISTS (SELECT Name FROM Character WHERE Name='NhamSV3' AND AccountID='adm_block')
BEGIN
DELETE Character WHERE Name='NhamSV3' AND AccountID<>'adm_block'
EXEC WZ_CreateCharacter 'adm_block', 'NhamSV3', 64
END
-- Tao nhan vat 4
IF NOT EXISTS (SELECT Name FROM Character WHERE Name='NhamSV4' AND AccountID='adm_block')
BEGIN
DELETE Character WHERE Name='NhamSV4' AND AccountID<>'adm_block'
EXEC WZ_CreateCharacter 'adm_block', 'NhamSV4', 80
END
-- Tao nhan vat 5
IF NOT EXISTS (SELECT Name FROM Character WHERE Name='NhamSV5' AND AccountID='adm_block')
BEGIN
DELETE Character WHERE Name='NhamSV5' AND AccountID<>'adm_block'
EXEC WZ_CreateCharacter 'adm_block', 'NhamSV5', 16
END

-- Block nhan vat thuoc tai khoan adm_block
UPDATE Character SET CtlCode=99 WHERE AccountID='adm_block' AND CtlCode<>99
-- END CHECK ACC ADM_BLOCK

SELECT @Acc=memb___id,@SV=ServerName FROM inserted WHERE ConnectStat='1'
SELECT @TheHe=thehe FROM MEMB_INFO WHERE memb___id=@Acc

-- BEGIN Khong ap dung The he
if(@slg_thehe=1)
BEGIN
UPDATE Character SET CtlCode=0 WHERE AccountID=@Acc AND CtlCode=99 AND khoado=0 AND AccountID!='adm_block' AND ErrorSubBlock!=99
UPDATE Character SET CtlCode=18 WHERE AccountID=@Acc AND CtlCode=99 AND khoado=1 AND AccountID!='adm_block' AND ErrorSubBlock!=99
UPDATE AccountCharacter SET GameID1='' WHERE GameID1='NhamSV1' AND Id=@Acc AND Id!='adm_block'
UPDATE AccountCharacter SET GameID2='' WHERE GameID2='NhamSV2' AND Id=@Acc AND Id!='adm_block'
UPDATE AccountCharacter SET GameID3='' WHERE GameID3='NhamSV3' AND Id=@Acc AND Id!='adm_block'
UPDATE AccountCharacter SET GameID4='' WHERE GameID4='NhamSV4' AND Id=@Acc AND Id!='adm_block'
UPDATE AccountCharacter SET GameID5='' WHERE GameID5='NhamSV5' AND Id=@Acc AND Id!='adm_block'
END
-- END Khong ap dung The he
ELSE
BEGIN
-- BEGIN Ap dung the he
-- The he 1
IF( @TheHe=1 )
BEGIN
IF( @SV='SV1-1' OR @SV='SV1-2' OR @SV='SV1-3' OR @SV='SV1-4' OR @SV='SV1-CS' OR @SV='LSV-CS' )
BEGIN
UPDATE Character SET CtlCode=0 WHERE AccountID=@Acc AND CtlCode=99 AND khoado=0 AND AccountID!='adm_block' AND ErrorSubBlock!=99
UPDATE Character SET CtlCode=18 WHERE AccountID=@Acc AND CtlCode=99 AND khoado=1 AND AccountID!='adm_block' AND ErrorSubBlock!=99
UPDATE AccountCharacter SET GameID1='' WHERE GameID1='NhamSV1' AND Id=@Acc AND Id!='adm_block'
UPDATE AccountCharacter SET GameID2='' WHERE GameID2='NhamSV2' AND Id=@Acc AND Id!='adm_block'
UPDATE AccountCharacter SET GameID3='' WHERE GameID3='NhamSV3' AND Id=@Acc AND Id!='adm_block'
UPDATE AccountCharacter SET GameID4='' WHERE GameID4='NhamSV4' AND Id=@Acc AND Id!='adm_block'
UPDATE AccountCharacter SET GameID5='' WHERE GameID5='NhamSV5' AND Id=@Acc AND Id!='adm_block'
END
ELSE
BEGIN
UPDATE Character SET CtlCode=99 WHERE AccountID!='adm_block' AND AccountID=@Acc AND (CtlCode=0 OR CtlCode=18) AND ErrorSubBlock!=99 AND AccountID NOT IN (SELECT DISTINCT AccountID FROM Character WHERE CtlCode = 32)
UPDATE AccountCharacter SET GameID1='NhamSV1' WHERE Id=@Acc AND (GameID1 IS NULL OR GameID1='') AND Id!='adm_block' AND Id NOT IN (SELECT DISTINCT AccountID FROM Character WHERE CtlCode = 32)
UPDATE AccountCharacter SET GameID2='NhamSV2' WHERE Id=@Acc AND (GameID2 IS NULL OR GameID2='') AND Id!='adm_block' AND Id NOT IN (SELECT DISTINCT AccountID FROM Character WHERE CtlCode = 32)
UPDATE AccountCharacter SET GameID3='NhamSV3' WHERE Id=@Acc AND (GameID3 IS NULL OR GameID3='') AND Id!='adm_block' AND Id NOT IN (SELECT DISTINCT AccountID FROM Character WHERE CtlCode = 32)
UPDATE AccountCharacter SET GameID4='NhamSV4' WHERE Id=@Acc AND (GameID4 IS NULL OR GameID4='') AND Id!='adm_block' AND Id NOT IN (SELECT DISTINCT AccountID FROM Character WHERE CtlCode = 32)
UPDATE AccountCharacter SET GameID5='NhamSV5' WHERE Id=@Acc AND (GameID5 IS NULL OR GameID5='') AND Id!='adm_block' AND Id NOT IN (SELECT DISTINCT AccountID FROM Character WHERE CtlCode = 32)
END
END
-- The he 2
ELSE IF( @TheHe=2 )
BEGIN
IF( @SV='SV2-1' OR @SV='SV2-2' OR @SV='SV2-3' OR @SV='SV2-4' OR @SV='SV2-CS' )
BEGIN
UPDATE Character SET CtlCode=0 WHERE AccountID=@Acc AND CtlCode=99 AND khoado=0 AND AccountID!='adm_block' AND ErrorSubBlock!=99
UPDATE Character SET CtlCode=18 WHERE AccountID=@Acc AND CtlCode=99 AND khoado=1 AND AccountID!='adm_block' AND ErrorSubBlock!=99
UPDATE AccountCharacter SET GameID1='' WHERE GameID1='NhamSV1' AND Id=@Acc AND Id!='adm_block'
UPDATE AccountCharacter SET GameID2='' WHERE GameID2='NhamSV2' AND Id=@Acc AND Id!='adm_block'
UPDATE AccountCharacter SET GameID3='' WHERE GameID3='NhamSV3' AND Id=@Acc AND Id!='adm_block'
UPDATE AccountCharacter SET GameID4='' WHERE GameID4='NhamSV4' AND Id=@Acc AND Id!='adm_block'
UPDATE AccountCharacter SET GameID5='' WHERE GameID5='NhamSV5' AND Id=@Acc AND Id!='adm_block'
END
ELSE
BEGIN
UPDATE Character SET CtlCode=99 WHERE AccountID!='adm_block' AND AccountID=@Acc AND (CtlCode=0 OR CtlCode=18) AND ErrorSubBlock!=99 AND AccountID NOT IN (SELECT DISTINCT AccountID FROM Character WHERE CtlCode = 32)
UPDATE AccountCharacter SET GameID1='NhamSV1' WHERE Id=@Acc AND (GameID1 IS NULL OR GameID1='') AND Id!='adm_block' AND Id NOT IN (SELECT DISTINCT AccountID FROM Character WHERE CtlCode = 32)
UPDATE AccountCharacter SET GameID2='NhamSV2' WHERE Id=@Acc AND (GameID2 IS NULL OR GameID2='') AND Id!='adm_block' AND Id NOT IN (SELECT DISTINCT AccountID FROM Character WHERE CtlCode = 32)
UPDATE AccountCharacter SET GameID3='NhamSV3' WHERE Id=@Acc AND (GameID3 IS NULL OR GameID3='') AND Id!='adm_block' AND Id NOT IN (SELECT DISTINCT AccountID FROM Character WHERE CtlCode = 32)
UPDATE AccountCharacter SET GameID4='NhamSV4' WHERE Id=@Acc AND (GameID4 IS NULL OR GameID4='') AND Id!='adm_block' AND Id NOT IN (SELECT DISTINCT AccountID FROM Character WHERE CtlCode = 32)
UPDATE AccountCharacter SET GameID5='NhamSV5' WHERE Id=@Acc AND (GameID5 IS NULL OR GameID5='') AND Id!='adm_block' AND Id NOT IN (SELECT DISTINCT AccountID FROM Character WHERE CtlCode = 32)
END
END
-- The he 3
ELSE IF( @TheHe=3 )
BEGIN
IF( @SV='SV3-1' OR @SV='SV3-2' OR @SV='SV3-3' OR @SV='SV3-4' OR @SV='SV3-CS' OR @SV='SVEvent-1' )
BEGIN
UPDATE Character SET CtlCode=0 WHERE AccountID=@Acc AND CtlCode=99 AND khoado=0 AND AccountID!='adm_block' AND ErrorSubBlock!=99
UPDATE Character SET CtlCode=18 WHERE AccountID=@Acc AND CtlCode=99 AND khoado=1 AND AccountID!='adm_block' AND ErrorSubBlock!=99
UPDATE AccountCharacter SET GameID1='' WHERE GameID1='NhamSV1' AND Id=@Acc AND Id!='adm_block'
UPDATE AccountCharacter SET GameID2='' WHERE GameID2='NhamSV2' AND Id=@Acc AND Id!='adm_block'
UPDATE AccountCharacter SET GameID3='' WHERE GameID3='NhamSV3' AND Id=@Acc AND Id!='adm_block'
UPDATE AccountCharacter SET GameID4='' WHERE GameID4='NhamSV4' AND Id=@Acc AND Id!='adm_block'
UPDATE AccountCharacter SET GameID5='' WHERE GameID5='NhamSV5' AND Id=@Acc AND Id!='adm_block'
END
ELSE
BEGIN
UPDATE Character SET CtlCode=99 WHERE AccountID!='adm_block' AND AccountID=@Acc AND (CtlCode=0 OR CtlCode=18) AND ErrorSubBlock!=99 AND AccountID NOT IN (SELECT DISTINCT AccountID FROM Character WHERE CtlCode = 32)
UPDATE AccountCharacter SET GameID1='NhamSV1' WHERE Id=@Acc AND (GameID1 IS NULL OR GameID1='') AND Id!='adm_block' AND Id NOT IN (SELECT DISTINCT AccountID FROM Character WHERE CtlCode = 32)
UPDATE AccountCharacter SET GameID2='NhamSV2' WHERE Id=@Acc AND (GameID2 IS NULL OR GameID2='') AND Id!='adm_block' AND Id NOT IN (SELECT DISTINCT AccountID FROM Character WHERE CtlCode = 32)
UPDATE AccountCharacter SET GameID3='NhamSV3' WHERE Id=@Acc AND (GameID3 IS NULL OR GameID3='') AND Id!='adm_block' AND Id NOT IN (SELECT DISTINCT AccountID FROM Character WHERE CtlCode = 32)
UPDATE AccountCharacter SET GameID4='NhamSV4' WHERE Id=@Acc AND (GameID4 IS NULL OR GameID4='') AND Id!='adm_block' AND Id NOT IN (SELECT DISTINCT AccountID FROM Character WHERE CtlCode = 32)
UPDATE AccountCharacter SET GameID5='NhamSV5' WHERE Id=@Acc AND (GameID5 IS NULL OR GameID5='') AND Id!='adm_block' AND Id NOT IN (SELECT DISTINCT AccountID FROM Character WHERE CtlCode = 32)
END
END
-- The he khac : block all sv
ELSE
BEGIN
UPDATE Character SET CtlCode=99 WHERE AccountID!='adm_block' AND AccountID=@Acc AND (CtlCode=0 OR CtlCode=18) AND ErrorSubBlock!=99 AND AccountID NOT IN (SELECT DISTINCT AccountID FROM Character WHERE CtlCode = 32)
UPDATE AccountCharacter SET GameID1='NhamSV1' WHERE Id=@Acc AND (GameID1 IS NULL OR GameID1='') AND Id!='adm_block' AND Id NOT IN (SELECT DISTINCT AccountID FROM Character WHERE CtlCode = 32)
UPDATE AccountCharacter SET GameID2='NhamSV2' WHERE Id=@Acc AND (GameID2 IS NULL OR GameID2='') AND Id!='adm_block' AND Id NOT IN (SELECT DISTINCT AccountID FROM Character WHERE CtlCode = 32)
UPDATE AccountCharacter SET GameID3='NhamSV3' WHERE Id=@Acc AND (GameID3 IS NULL OR GameID3='') AND Id!='adm_block' AND Id NOT IN (SELECT DISTINCT AccountID FROM Character WHERE CtlCode = 32)
UPDATE AccountCharacter SET GameID4='NhamSV4' WHERE Id=@Acc AND (GameID4 IS NULL OR GameID4='') AND Id!='adm_block' AND Id NOT IN (SELECT DISTINCT AccountID FROM Character WHERE CtlCode = 32)
UPDATE AccountCharacter SET GameID5='NhamSV5' WHERE Id=@Acc AND (GameID5 IS NULL OR GameID5='') AND Id!='adm_block' AND Id NOT IN (SELECT DISTINCT AccountID FROM Character WHERE CtlCode = 32)
END
-- END Ap dung the he
END
END



Tên Sub mình đặt các sub chuẩn nha.

Rồi Phần Web cũng ok rồi Nhưng để như này chỉ được TH1 vào không bị khóa còn các tk TH2 và TH3 đều bị khóa hết và NhamSV.
Đã tạo adm_block và nhamsv chạy query.

Ai Giúp mình với nhé

Xin cảm ơn