事务日志记录信息

--sp_helpdb 
----TestDB=7

--DBCC LOG(db_id,formart_id) 
/*
--db_id：数据库编号,可以用sp_helpdb得到
--formart_id：日志记录的方式[1,2,3]
*/

DBCC LOG(7,1) 

 

USE TestDB
GO

--创建新表
CREATE TABLE TUsers
(
    FID INT IDENTITY(1,1) PRIMARY KEY,
    FAccount VARCHAR(38),
    FLogonPass VARCHAR(32),
    FCompanyID INT,
    FGradeID INT,
    FStatus TINYINT,
    FRegisterDate DATETIME,
    FLastLogonIP VARCHAR(20)
)
GO


CHECKPOINT
GO
--清空TestDB数据库的日志文件
BACKUP LOG TestDB WITH TRUNCATE_ONLY
GO


DBCC LOG(7,1) 
GO

 

--表新增一行数据
INSERT INTO TUsers(FAccount,FLogonPass,FCompanyID,FGradeID,FStatus,FRegisterDate,FLastLogonIP)
VALUES('admin','9a84ee41aa72de59c63006aad670bcce',123,-1,1,GETDATE(),'127.0.0.1');
GO

DBCC LOG(7,1) 
GO

 

--表更新一行数据
UPDATE TUsers SET FAccount='rose'
WHERE FID=1
GO

DBCC LOG(7,1) 
GO

 

--表删除一行数据
DELETE TUsers 
WHERE FID=1
GO

DBCC LOG(7,1) 
GO

 

--新增3行数据
INSERT INTO TUsers(FAccount,FLogonPass,FCompanyID,FGradeID,FStatus,FRegisterDate,FLastLogonIP)
VALUES('rose','9a84ee41aa72de59c63006aad670bcce',30000,-1,1,GETDATE(),'127.0.0.1');

INSERT INTO TUsers(FAccount,FLogonPass,FCompanyID,FGradeID,FStatus,FRegisterDate,FLastLogonIP)
VALUES('rose','9a84ee41aa72de59c63006aad670bcce',30000,-1,1,GETDATE(),'127.0.0.1');

INSERT INTO TUsers(FAccount,FLogonPass,FCompanyID,FGradeID,FStatus,FRegisterDate,FLastLogonIP)
VALUES('rose','9a84ee41aa72de59c63006aad670bcce',30000,-1,1,GETDATE(),'127.0.0.1');

GO


DBCC LOG(7,1) 
GO

 

--事务,批量新增三行数据
BEGIN TRAN

    INSERT INTO TUsers(FAccount,FLogonPass,FCompanyID,FGradeID,FStatus,FRegisterDate,FLastLogonIP)
    VALUES('rose','9a84ee41aa72de59c63006aad670bcce',43672,-1,1,GETDATE(),'127.0.0.1');

    INSERT INTO TUsers(FAccount,FLogonPass,FCompanyID,FGradeID,FStatus,FRegisterDate,FLastLogonIP)
    VALUES('rose','9a84ee41aa72de59c63006aad670bcce',43672,-1,1,GETDATE(),'127.0.0.1');

    INSERT INTO TUsers(FAccount,FLogonPass,FCompanyID,FGradeID,FStatus,FRegisterDate,FLastLogonIP)
    VALUES('rose','9a84ee41aa72de59c63006aad670bcce',43672,-1,1,GETDATE(),'127.0.0.1');

COMMIT TRAN
GO

DBCC LOG(7,1) 
GO

 

--表删除多行数据
DELETE TUsers 
WHERE FCompanyID=43672;
GO

DBCC LOG(7,1) 
GO

 

--事务,两条更新语句(各更新多行数据)
BEGIN TRAN

    UPDATE dbo.TUsers SET FGradeID=2
    WHERE FCompanyID=43672;

    UPDATE dbo.TUsers SET FGradeID=2
    WHERE FCompanyID=36052;

COMMIT TRAN
GO

DBCC LOG(7,1) 
GO

 

--事务,两条删除语句(各删除多行数据)
BEGIN TRAN

    DELETE TUsers 
    WHERE FCompanyID=43672;

    DELETE TUsers 
    WHERE FCompanyID=36052;

COMMIT TRAN
GO

DBCC LOG(7,1) 
GO

 

--清空表
TRUNCATE TABLE TUsers
GO

DBCC LOG(7,1) 
GO
--25行日志记录

 

--删除一张表
DROP TABLE TUsers
GO

DBCC LOG(7,1) 
GO
--88行日志记录

 

--清空表并删除表
TRUNCATE TABLE TUsers
GO

DROP TABLE dbo.TUsers
GO

DBCC LOG(7,1) 
GO
--99行日志记录