记录数据库操作日志问题，一下sql不大明白，请sql版的童鞋指教，谢谢
SQL code--1、先为数据库建立一个字段试图，所有数据都是从系统表中提取，便于以后用户可以扩展系统功能。 Java代码 CREATE VIEW dbo.V_SystemColumn AS SELECT DISTINCT TOP 100 PERCENT dbo.sysobjects.name AS TableName, dbo.sysobjects.id, dbo.sysobjects.xtype, dbo.syscolumns.name AS ColumnName, dbo.syscolumns.colid, dbo.syscolumns.type, dbo.syscolumns.colstat FROM dbo.sysobjects INNER JOIN dbo.syscolumns ON dbo.sysobjects.id = dbo.syscolumns.id WHERE (dbo.sysobjects.xtype = 'U') ORDER BY dbo.sysobjects.id, dbo.syscolumns.colid 2、建立一个各个表之间关联的视图。 Sql代码 CREATE VIEW dbo.V_Reference AS SELECT DISTINCT TOP 100 PERCENT o1.name AS PK_TABLE_NAME, c1.name AS PK_COLUMN_NAME, o2.name AS FK_TABLE_NAME, c2.name AS FK_COLUMN_NAME FROM dbo.sysobjects o1 INNER JOIN dbo.sysreferences r ON o1.id = r.rkeyid INNER JOIN dbo.syscolumns c1 ON o1.id = c1.id AND r.rkey1 = c1.colid INNER JOIN dbo.sysobjects o2 ON r.fkeyid = o2.id INNER JOIN dbo.syscolumns c2 ON o2.id = c2.id AND r.fkey1 = c2.colid INNER JOIN dbo.sysindexes i ON r.rkeyid = i.id AND r.rkeyindid = i.indid WHERE (permissions(o1.id) <> 0) AND (permissions(o2.id) <> 0) ORDER BY FK_Table_Name 3、创建一个存取过程，参数为：表名、列名、Insert.列名的值、返回参数Sql代码 CREATE Procedure GetColumnValue @FKTableName Varchar(128), @FKColumnName Varchar(128), @FKValue Varchar(8000), @ReturnValue Varchar(8000) OUTPUT AS declare @PkTableName Varchar(128) declare @PkColumnName Varchar(128) declare @PkDescriptionName Varchar(128) declare @SqlText Varchar(8000) declare @ret varchar(8000) --获取关联主表的表名和字段名 select @PkTableName=Pk_Table_Name,@PkColumnName=Pk_Column_Name from V_Reference Where [email protected] and [email protected] if(@PkTableName is null) begin Select @[email protected] return 0 end else begin Select Top 1 @PkDescriptionName=ColumnName from V_SystemColumn Where [email protected] and ColumnName like '%Name' Create Table #temp (PkDescriptionName Varchar(8000) ) select @SqlText=' Insert Into #temp Select [email protected] select @[email protected]+' from [email protected] select @[email protected]+' Where [email protected][email protected]+'''' execute(@SqlText) select @ReturnValue=PkDescriptionName from #temp end GO 4、为系统创建记录日志的表Sql代码 CREATE TABLE T_SystemLog ( TableName varchar(128) NULL, KeyValue varchar(20) NOT NULL, FieldName varchar(128) NULL, OldValue varchar(8000) NULL, NewValue varchar(8000) NULL, Modifier varchar(20) NULL, ModifyDate datetime NULL DEFAULT CURRENT_TIMESTAMP ) go 5、创建日志记录存取过程Sql代码 CREATE Procedure Logger @TableName Varchar(128), @ColumnName Varchar(128), @KeyValue int, @OldValue Varchar(8000), @NewValue Varchar(8000), @LastModifier Varchar(20) AS if(@OldValue<>@NewValue) begin exec GetColumnValue @TableName,@ColumnName,@OldValue,@OldValue Output exec GetColumnValue @TableName,@ColumnName,@NewValue,@NewValue Output Insert Into T_SystemLog(TableName,KeyValue,FieldName,OldValue,NewValue,Modifier,ModifyDate) Values( @TableName,@KeyValue,@ColumnName,@OldValue,@NewValue,@LastModifier,getdate()) end GO 6、为需要记录修改日志的表创建Insert、Update触发器Sql代码 CREATE trigger uti_corp on T_Corp for Update AS set nocount on declare @KeyValue int declare @OldValue Varchar(8000) declare @NewValue varchar(8000) declare @LastModifier varchar(8000) if(update(departmentid)) ----这里怎么理解啊?对一个表来说，是不是要列出来所有字段？begin select @KeyValue=corpid,----这里怎么理解啊?@NewValue=departmentid,----这里怎么理解啊?@LastModifier=LastModifier ----这里怎么理解啊?能判断执行的sql所在的客户端么？什么原理？from inserted select @OldValue=departmentid ----这里怎么理解啊?from deleted execute Logger 'T_Corp','DepartmentID',@KeyValue,@OldValue,@NewValue,@LastModifier end