 CREATE ROLE [role2];
 CREATE ROLE [roleu];
 
---GRANT CREATE TRIGGER
---ON [ChinaMobile]
---TO [rolem]
---WITH GRANT OPTION;
 GRANT INSERT, DELETE, SELECT, UPDATE, ALTER, REFERENCES
 ON [BHall]
 TO [rolem]

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

SETUSER;


SETUSER 'manager';
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 [pnumber], SUM([mtotal]), DATEPART(YEAR, [mdate]) AS [year], DATEPART(MONTH, [mdate]) AS [month] FROM [phoneMFee] GROUP BY [pnumber], DATEPART(YEAR, [mdate]), DATEPART(MONTH, [mdate]);
SELECT * FROM [cellUser], [phoneFeeHistory] WHERE [cellUser].[pnumber] = [phoneFeeHistory].[pnumber] AND [pbalance] < 0;
SETUSER;


SETUSER 'sales1';
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 [pnumber], SUM([mtotal]), DATEPART(YEAR, [mdate]) AS [year], DATEPART(MONTH, [mdate]) AS [month] FROM [phoneMFee] GROUP BY [pnumber], DATEPART(YEAR, [mdate]), DATEPART(MONTH, [mdate]);
SELECT * FROM [cellUser], [phoneFeeHistory] WHERE [cellUser].[pnumber] = [phoneFeeHistory].[pnumber] AND [pbalance] < 0;
SETUSER;


SETUSER 'oper1';
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 [pnumber], SUM([mtotal]), DATEPART(YEAR, [mdate]) AS [year], DATEPART(MONTH, [mdate]) AS [month] FROM [phoneMFee] GROUP BY [pnumber], DATEPART(YEAR, [mdate]), DATEPART(MONTH, [mdate]);
SELECT * FROM [cellUser], [phoneFeeHistory] WHERE [cellUser].[pnumber] = [phoneFeeHistory].[pnumber] AND [pbalance] < 0;
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 [pnumber], SUM([mtotal]), DATEPART(YEAR, [mdate]) AS [year], DATEPART(MONTH, [mdate]) AS [month] FROM [phoneMFee] GROUP BY [pnumber], DATEPART(YEAR, [mdate]), DATEPART(MONTH, [mdate]);
SELECT * FROM [cellUser], [phoneFeeHistory] WHERE [cellUser].[pnumber] = [phoneFeeHistory].[pnumber] AND [pbalance] < 0;
SETUSER;


REVOKE INSERT
ON [BHall]
