--�������������ݿ�AuthorityDB sql
if Exists(select top 1 * from sysObjects where Id=OBJECT_ID(N'MACLogs') and xtype='U')
    drop table MACLogs
go
create table MACLogs(
Id int identity(1,1),
HostsId int not  null,
MAC nvarchar(50) null,
userid int not null,
location  nvarchar(100) null,
Ip nvarchar(50) null,
ActionId  INT null,
[type] int default 0, --  0 ����  1 ���  2 ����
CreateTime datetime  default getdate()--  0 ����  1 ���  
)
go
--�������������ݿ�AuthorityDB sql
if Exists(select top 1 * from sysObjects where Id=OBJECT_ID(N'ErrorInfo') and xtype='U')
    drop table ErrorInfo
go
create table ErrorInfo(
Id int identity(1,1),
HostsId int not  null,
MAC nvarchar(50) null,
userid int not null,
[type] int default 0, --  0 ���䱻��  1 MAC����
[status] int default 0,--�Ƿ��Ѿ����� 0 δ����  1 �Ѿ�����
CreateTime datetime  default getdate()--  ʱ��
)
go

select * from Hosts where id = 14939

SELECT * FROM MACLogs  
if EXISTS(SELECT 1 FROM sys.views WHERE name='View_MAClog')
begin
drop view View_MAClog
end
go
create view View_MAClog
as 
select m.*,u.[Uid] ,ht.Name,Hotelsid = ht.Id  
from MACLogs m 
left join Hotels ht on ht.id = m.HotelID 
left join UserInfo u on u.id  = m.userid
go
select *,CreateTime from MACLogs
SELECT * FROM View_MAClog
SELECT * FROM ErrorInfo --WHERE 
SELECT * FROM UserInfo WHERE ID = 212
--�� sql
select * from tb_Hosts where hotelid = 3

update  tb_Hosts  set  MAC=MAC where MAC= '34-D0-B8-11-10-4D';update  tb_Hosts  set  MAC='34-D0-B8-11-10-4D' where Id = 14810;
DECLARE @MAC int;
DECLARE @ID int;
 --����  @MAC  �µ�MAC    @ID  Ϊ��Ҫ����mac��id
                                begin tran 
                                DECLARE @HostsId int;
                                DECLARE @oldmac nvarchar(50);
                                DECLARE @HostsIdoldmac nvarchar(50);
                                SET @HostsId = -1;
                                SET @oldmac = '-1';
                                SET @HostsIdoldmac = '-1';
                                SELECT TOP 1 @HostsId  = id, @oldmac= MAC FROM tb_Hosts  where  MAC = @MAC
                                SELECT TOP 1 @HostsIdoldmac  = MAC FROM tb_Hosts  where  Id = @ID  and mac is not null and mac!=''
                                PRINT convert(varchar,@HostsId) +'&'+ @oldmac+'&'+@HostsIdoldmac
                                update  tb_Hosts  set  MAC='' where id = @HostsId
                                update  tb_Hosts  set  MAC=@MAC where Id = @ID
                                if @@ERROR>0
                                begin
                                rollback tran
                                end
                                ELSE
                                BEGIN
                                COMMIT TRAN
                                END

								;

								go

							select * from Hosts where Id = 14939