--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 FK_Table_Name=@FKTableName and   
         FK_Column_Name=@FKColumnName  
  
if(@PkTableName is null)  
begin  
  Select @ReturnValue=@FKValue  
  return 0  
end  
else  
begin  
  Select Top 1 @PkDescriptionName=ColumnName   
    from V_SystemColumn   
    Where TableName=@PkTableName and ColumnName like '%Name'  
  
  
 Create Table #temp  
   (PkDescriptionName Varchar(8000) )  
  
  
  select @SqlText='   Insert Into #temp Select  '+@PkDescriptionName   
  select @SqlText=@SqlText+'    from '+@PkTableName  
  select @SqlText=@SqlText+'   Where '+@PkColumnName+'='+''''+@FKValue+''''  
  
  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