 AFTER INSERT
 AS
 BEGIN
-  IF EXISTS (SELECT i.[pnumber] FROM [phoneFeeHistory], inserted AS i)
+  IF EXISTS (SELECT i.[pnumber] FROM [phoneFeeHistory], Inserted AS i)
   BEGIN
     INSERT INTO [phoneFeeHistory]
 	SELECT TOP 1 [pnumber], [pmonth], [fee], [type], [pbalance]

SETUSER;


SETUSER 'oper1';
SELECT * FROM [phoneMFee];
SETUSER;


SETUSER 'user1';
SELECT * FROM [SaleRecord];
SELECT * FROM [cellUser];
SELECT * FROM [phoneMFee];
SELECT * FROM [cellphone], [SaleRecord] WHERE [cellphone].[Ptype] = [SaleRecord].[Ptype];
SELECT * FROM [BHall], [SaleRecord] WHERE [BHall].[Hno] = [SaleRecord].[Hno];
SELECT * FROM [cellUser] WHERE [pnumber] = '13723332333';
SELECT * FROM [phoneMFee];
SELECT * FROM [cellUser], [phoneFeeHistory] WHERE [cellUser].[pnumber] = [phoneFeeHistory].[pnumber] AND [pbalance] < 0;
SELECT * FROM [phoneFeeHistory];
SETUSER;

REVOKE INSERT
ON [BHall]

WITH GRANT OPTION;
GO


EXEC sp_addrolemember N'rolem', N'manager'
GO
EXEC sp_addrolemember N'role1', N'sales1'
GO
EXEC sp_addrolemember N'role1', N'sales2'
GO
EXEC sp_addrolemember N'role1', N'sales3'
GO
EXEC sp_addrolemember N'role2', N'oper1'
GO
EXEC sp_addrolemember N'role2', N'oper2'
GO
EXEC sp_addrolemember N'roleu', N'user1'
GO

SETUSER 'manager';
GRANT INSERT, DELETE, SELECT, UPDATE, ALTER
ON [BHall]
TO [role1]
AS [rolem];
GRANT INSERT, DELETE, SELECT, UPDATE, ALTER
ON [cellphone]
TO [role1]
AS [rolem];
GRANT INSERT, DELETE, SELECT, UPDATE, ALTER
ON [SaleRecord]
TO [role1]
AS [rolem];
GO

GRANT INSERT, SELECT, DELETE, UPDATE([username], [uid], [email]), ALTER
ON [cellUser]
TO [role2]
AS [rolem];
GRANT INSERT, SELECT, DELETE, UPDATE([mdate], [mfee1], [mfee2], [mfee3], [mtotal]), ALTER
ON [phoneMFee]
TO [role2]
AS [rolem];
GRANT INSERT, SELECT, DELETE, UPDATE([pdate], [paid]), ALTER
ON [phonePaid]
TO [role2]
AS [rolem];
GRANT SELECT
ON [phoneFeeHistory]
TO [role2]
AS [rolem];
GO

GRANT SELECT
ON [cellphone]
TO [roleu]
AS [rolem];
GRANT SELECT
ON [cellUser]
TO [roleu]
AS [rolem];
GRANT SELECT
ON [phoneMFee]
TO [roleu]
AS [rolem];
GRANT SELECT
ON [phonePaid]
TO [roleu]
AS [rolem];
GRANT SELECT
ON [phoneFeeHistory]
TO [roleu]
AS [rolem];
GO

GO
CREATE TRIGGER phoneFeeHistory_AfterInsertphoneMFee_TRG 
