好像你都已经做完了，难道你的问题是数据库里的表的函数，是怎么定义的，写在哪里，怎么被调用吗？我感觉你的问题是不太明白ddl（数据定义语言）,与dml（数据操纵语言）的写在哪里，怎么被调用，他们的运行机制的问题。
) 。 这是dml。真的好感动！谢谢您打了这么多字，这还是您的第一个帖子。好像你都已经做完了，难道你的问题是数据库里的表的函数，是怎么定义的，写在哪里，怎么被调用吗？我感觉你的问题是不太明白ddl（数据定义语言）,与dml（数据操纵语言）的写在哪里，怎么被调用，他们的运行机制的问题。
让我们理一下这个过程，
1、建立一个辅助表，分别记录insert, update, delete，这个需要在你说的主表上建立trigger（其实也可以用的别方法）, 你已经完成了。
2、从这个辅助表里面，a、把所需的记录根据查询条件取出来，b、要求用一个 function来实现。
a、就是查询语句，就是select * from table where condition in [.......] desc by field, 类似这个样子吧（我好多年不写程序了，记不住精确的语法了
b、怎么把a的语句放到一个function里，并将查询条件传入。
---这涉及到function的含义，时间上你可以理解database里有两种function.
b-1：在database里，表可以有自己的function， 定义一个表的function，这个叫ddl。在这个function里可以写dml语言。 但是，表的function里是不接受参数，我好像记得不接受。（当然，各种数据库上可能会有差异）
b-2: storage procedure. 这里定义函数(DDL)，接受参数,并返回数据集。我觉得你的问题在存储过程上写一个函数，在函数体内按要求写查询语句，返回结果集就好。
c、一点建议，一般程序员不会重复的去写ddl语句，能看懂就行了。这些ddl都是工具产生的，面试的时候也不会去刻意问你ddl,都是问些dml。你下个navcat或者用access，在上面图像化的界面上操作一下，然后看相应应的dml就明白了。
优化，
简单的说吧，1、patch，只记录不同。然后再function里来生成数据集返回。
2、取决于系统架构和软件结构，是开timer还是用线程，怎么预先生成？等等。。
3、
CREATE FUNCTION [dbo].[data1_PIT](@data_date smalldatetime, @as_of_date datetime)
RETURNS TABLE
AS
RETURN
SELECT
IF EXISTS (SELECT * FROM data_audit.data1 AS DA WHERE DA.date = @data_date AND DA.ChangeDate > @as_of_date)
WITH cte AS
(
SELECT DA.*, ROW_NUMBER() OVER(PARTITION BY DA.id_security ORDER BY DA.ChangeDate) AS rk
FROM data_audit.data1 AS DA WHERE DA.date=@data_date AND DA.ChangeDate > @as_of_date)
)
SELECT date,
id_security,
column6,
column7,
column10,
column11,
column12,
column13,
column14,
column15,
column16,
column17,
column18,
column19,
column20,
column21,
column22,
column23,
column24,
column25,
column26,
column27,
column28,
column29,
column30,
column31,
column32,
column33,
column34,
column35,
column36,
column37,
column38,
column39,
column40,
column41,
column42,
column43,
column44,
column45,
AddLoginID,
AddDate,
column48,
column49,
column50,
column51,
column52,
column53,
column54,
column55,
column56,
column57,
column58,
column59,
column60,
column61,
column62,
column64,
column65,
column66,
column67,
column68,
column69,
column70,
column71,
column72
FROM cte
where rk = 1;
ELSE
SELECT D.* FROM dbo.data1 AS D WHERE date = @data_date
END
。
谢谢美女姐姐！我看了一下您的function的参数是originalDate和changeDate，但题目要求是dbo.data1_PIT 可以重现过去某时刻的select并return出exact same result，我们是不知道ChangeDate的，只知道我们想重现过去某个时间点主table select出来的数据。三个trigger已经写好了，现在问题就只剩这个function dbo.data1_PIT(@data_date smalldatetime, @as_of_date datetime).首先指出我和楼主对题目理解不同的几点：
1. In the future, even though records can be updated or deleted-所以我认为这里只是指Update 和Delete 而不包括insert。
2. I do SELECT * FROM dbo.data1_PIT('2011-12-31', '2018-02-22 14:42:22')-这里的dbo.data1_PIT 是function的名字，不是指原始的table: data1；题目中并没有说history log要从原始table data1中选出. 我是这样理解的，用delete 做例子，既然delete已经发生，原始data1 table里就不会再存在那条信息，又如何能从原始table: data1里select那条信息呢？ 别的公司我不知道， 我们公司是原始table delete records, 然后只在audit table 中保留历史记录，用于万一需要revert的时候用。
帮楼主把Trigger 和Function写好了， 也在我电脑上简单测试了一下，运行没问题。 刚毕业都不容易，特别是女生做developer可能会更难一些。祝楼主好运
---Create Trigger-I used delete as an an example；but this trigger can be modified to use for all update/delete/insert.
Create trigger Demo_Trigger
on data1
for delete
as
begin
set nocount on
insert into data_audit_data1
(date, id_security,changedate,ChangeUser,column6,column7)
select date, id_security,getdate(),SUser_SName(),column6,column7 ---I only used two columns as example, but you can reference all the columns
from deleted
end
go
---Test--Delete any row from data1 to confirm the history log will be inserted into the audit table: data_audit_data1
delete from data1
where date='1976-12-31' and id_security=57 and column6='2030' ---This is the first row in your sample table: data1; you can delete any other row as well.
---Create function: dbo.data1_PIT
Create function dbo.data1_PIT
(@OriginalDate datetime, @ChangeDate datetime)
returns table
as
return
select date,id_security,column6, column7 from data_audit_data1 where date=@OriginalDate and changedate=@ChangeDate
go
---Use function: dbo.data1_PIT to retrieve data that has been deleted above (See attached screenshot below for testing result)
select * from dbo.data1_PIT ('1976-12-31 00:00:00.000','2018-04-21 16:13:52.430')
USE [store]
GO
/****** Object: Trigger [dbo].[trg_data1_Delete_all_fields] Script Date: 4/21/2018 4:51:30 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[trg_data1_Delete_all_fields]
ON [dbo].[data1]
FOR DELETE
AS
SET NOCOUNT ON
DECLARE @uid INT
SET @uid = SUSER_ID()
-- [ChangeAction]=D means delete
INSERT INTO [data_audit].[data1] ( [ChangeAction], [ChangeDate], [ChangeUser], [date],[id_security],[column6],[column7],[column10],[column11],[column12],[column13],[column14],[column15],[column16],[column17],[column18],[column19],[column20],[column21],[column22],[column23],[column24],[column25],[column26],[column27],[column28],[column29],[column30],[column31],[column32],[column33],[column34],[column35],[column36],[column37],[column38],[column39],[column40],[column41],[column42],[column43],[column44],[column45],[AddLoginID],[AddDate],[column48],[column49],[column50],[column51],[column52],[column53],[column54],[column55],[column56],[column57],[column58],[column59],[column60],[column61],[column62],[column64],[column65],[column66],[column67],[column68],[column69],[column70],[column71],[column72] )
SELECT 'D' AS [ChangeAction],getdate() AS [ChangeDate], @uid AS [ChangeUser], [date],[id_security],[column6],[column7],[column10],[column11],[column12],[column13],[column14],[column15],[column16],[column17],[column18],[column19],[column20],[column21],[column22],[column23],[column24],[column25],[column26],[column27],[column28],[column29],[column30],[column31],[column32],[column33],[column34],[column35],[column36],[column37],[column38],[column39],[column40],[column41],[column42],[column43],[column44],[column45],[AddLoginID],[AddDate],[column48],[column49],[column50],[column51],[column52],[column53],[column54],[column55],[column56],[column57],[column58],[column59],[column60],[column61],[column62],[column64],[column65],[column66],[column67],[column68],[column69],[column70],[column71],[column72]
FROM deletedUSE [store]
GO
/****** Object: Trigger [dbo].[trg_data1_Insert_all_fields] Script Date: 4/21/2018 4:52:21 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[trg_data1_Insert_all_fields]
ON [dbo].[data1]
FOR INSERT
AS
SET NOCOUNT ON
DECLARE @uid INT
SET @uid = SUSER_ID()
--inserts only - we just need to update user information
UPDATE u
SET
u.[AddLoginID]=@uid,
u.[AddDate]=getdate()
FROM [dbo].[data1] u
INNER JOIN inserted i ON
i.[date] = u. [date] AND i.[id_security] = u. [id_security]
WHERE u.[AddLoginID] IS NULL AND u.[AddDate] IS NULLUSE [store]
GO
/****** Object: Trigger [dbo].[trg_data1_Update_all_fields] Script Date: 4/21/2018 4:52:41 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[trg_data1_Update_all_fields]
ON [dbo].[data1]
FOR UPDATE
AS
SET NOCOUNT ON
DECLARE @uid INT
SET @uid = SUSER_ID()
IF ( SELECT trigger_nestlevel()
) = 1 --don't do anything if triggered from insert trigger
BEGIN
--[ChangeAction]=U means update
INSERT INTO [data_audit].[data1] ( [ChangeAction], [ChangeDate], [ChangeUser], [date],[id_security],[column6],[column7],[column10],[column11],[column12],[column13],[column14],[column15],[column16],[column17],[column18],[column19],[column20],[column21],[column22],[column23],[column24],[column25],[column26],[column27],[column28],[column29],[column30],[column31],[column32],[column33],[column34],[column35],[column36],[column37],[column38],[column39],[column40],[column41],[column42],[column43],[column44],[column45],[AddLoginID],[AddDate],[column48],[column49],[column50],[column51],[column52],[column53],[column54],[column55],[column56],[column57],[column58],[column59],[column60],[column61],[column62],[column64],[column65],[column66],[column67],[column68],[column69],[column70],[column71],[column72] )
SELECT 'U' AS [ChangeAction],getdate() AS [ChangeDate], @uid AS [ChangeUser], [date],[id_security],[column6],[column7],[column10],[column11],[column12],[column13],[column14],[column15],[column16],[column17],[column18],[column19],[column20],[column21],[column22],[column23],[column24],[column25],[column26],[column27],[column28],[column29],[column30],[column31],[column32],[column33],[column34],[column35],[column36],[column37],[column38],[column39],[column40],[column41],[column42],[column43],[column44],[column45],[AddLoginID],[AddDate],[column48],[column49],[column50],[column51],[column52],[column53],[column54],[column55],[column56],[column57],[column58],[column59],[column60],[column61],[column62],[column64],[column65],[column66],[column67],[column68],[column69],[column70],[column71],[column72]FROM
(
SELECT [date],[id_security],[column6],[column7],[column10],[column11],[column12],[column13],[column14],[column15],[column16],[column17],[column18],[column19],[column20],[column21],[column22],[column23],[column24],[column25],[column26],[column27],[column28],[column29],[column30],[column31],[column32],[column33],[column34],[column35],[column36],[column37],[column38],[column39],[column40],[column41],[column42],[column43],[column44],[column45],[AddLoginID],[AddDate],[column48],[column49],[column50],[column51],[column52],[column53],[column54],[column55],[column56],[column57],[column58],[column59],[column60],[column61],[column62],[column64],[column65],[column66],[column67],[column68],[column69],[column70],[column71],[column72]
FROM deleted
EXCEPT
SELECT [date],[id_security],[column6],[column7],[column10],[column11],[column12],[column13],[column14],[column15],[column16],[column17],[column18],[column19],[column20],[column21],[column22],[column23],[column24],[column25],[column26],[column27],[column28],[column29],[column30],[column31],[column32],[column33],[column34],[column35],[column36],[column37],[column38],[column39],[column40],[column41],[column42],[column43],[column44],[column45],[AddLoginID],[AddDate],[column48],[column49],[column50],[column51],[column52],[column53],[column54],[column55],[column56],[column57],[column58],[column59],[column60],[column61],[column62],[column64],[column65],[column66],[column67],[column68],[column69],[column70],[column71],[column72]
FROM inserted
) t
END首先指出我和楼主对题目理解不同的几点：
1. In the future, even though records can be updated or deleted-所以我认为这里只是指Update 和Delete 而不包括insert。
2. I do SELECT * FROM dbo.data1_PIT('2011-12-31', '2018-02-22 14:42:22')-这里的dbo.data1_PIT 是function的名字，不是指原始的table: data1；题目中并没有说history log要从原始table data1中选出. 我是这样理解的，用delete 做例子，既然delete已经发生，原始data1 table里就不会再存在那条信息，又如何能从原始table: data1里select那条信息呢？ 别的公司我不知道， 我们公司是原始table delete records, 然后只在audit table 中保留历史记录，用于万一需要revert的时候用。
帮楼主把Trigger 和Function写好了， 也在我电脑上简单测试了一下，运行没问题。 刚毕业都不容易，特别是女生做developer可能会更难一些。祝楼主好运
---Create Trigger-I used delete as an an example；but this trigger can be modified to use for all update/delete/insert.
Create trigger Demo_Trigger
on data1
for delete
as
begin
set nocount on
insert into data_audit_data1
(date, id_security,changedate,ChangeUser,column6,column7)
select date, id_security,getdate(),SUser_SName(),column6,column7 ---I only used two columns as example, but you can reference all the columns
from deleted
end
go
---Test--Delete any row from data1 to confirm the history log will be inserted into the audit table: data_audit_data1
delete from data1
where date='1976-12-31' and id_security=57 and column6='2030' ---This is the first row in your sample table: data1; you can delete any other row as well.
---Create function: dbo.data1_PIT
Create function dbo.data1_PIT
(@OriginalDate datetime, @ChangeDate datetime)
returns table
as
return
select date,id_security,column6, column7 from data_audit_data1 where date=@OriginalDate and changedate=@ChangeDate
go
---Use function: dbo.data1_PIT to retrieve data that has been deleted above (See attached screenshot below for testing result)
select * from dbo.data1_PIT ('1976-12-31 00:00:00.000','2018-04-21 16:13:52.430')
。