使用触发器获取日期时间而不是客户日期时间 [英] Use trigger for get date time instead client date time

问题描述

嘿伙计

i使用以下触发器获取服务器中的日期时间

  USE  [DBStore] 
  GO  
 
  SET   ANSI_NULLS   ON  
  GO  
  SET   QUOTED_IDENTIFIER   ON  
  GO  
 
 创建  TRIGGER  [dbo]。[ServerTime]  ON  [dbo]。[TblSellDocuments] 
 INSTEAD  OF   INSERT  
  AS  
 开始尝试
  SELECT  *  INTO  #Inserted  FROM 已插入
 更新 #Inserted  SET  Datein = GETDATE（）
  INSERT   INTO  TblSellDocuments  SELECT  *  FROM  #Inserted 
 
 结束尝试
 开始 Catch 
 
 结束 catch 

但这不起作用我有这个错误

显式值  身份 列    table  '  TblSellDocuments'只能指定  a 列 list   和  IDENTITY_INSERT     ON 。

解决方案

一个显式值换的身份-列在表-可以仅待指定-时-A＃2005527 [ ^ ]



查看此链接以获取解决方案。

< blockquote>我建​​议你阅读这篇文章：日期和时间函数（ Transact-SQL） [ ^ ]。有几种方法可以获取服务器的日期和时间。



您使用触发器更新 DateIn 字段已插入表。您不需要使用触发器。您可以使用简单的更新 [ ^ ]声明：

 更新已插入 SET  Datein = GETDATE（）< span class =code-comment>  -   或其他功能 

但如果你想要将数据从一个表插入另一个表，使用 INSERT 与 SELECT ：

  INSERT   INTO  TblSellDocuments（Field1，Field2，DateTimeField）
  SELECT  Field1，Field2，GETDATE（） AS  DateTimeField 
  FROM 已插入

这就是全部！

错误本身是自我解释...它说你不能在IDENTITY INSERT为OFF的基表中插入标识列。

如果INSTEAD OF INSERT视图触发器对基表生成INSERT使用插入表中的数据，它必须忽略这些类型的列（标识列，带有时间戳数据类型的列）的值，方法是不包括INSERT语句的选择列表中的列。 INSERT语句可以为这些类型的列生成虚拟值。



所以我建议你使用设置IDENTITY_INSERT ON 或者不要尝试为这样的标识列插入值..

   -   跳过ID和ComputedCol列的正确INSERT语句。 
  INSERT   INTO  BaseTable（颜色，材质）
  VALUES （N '  Red'，N '  Cloth'）; 

   -   < span class =code-comment>尝试为 
提供值的错误语句  -    ID和ComputedCol列。 
  INSERT   INTO  BaseTable 
  VALUES （  2 ，N '  Green'，N '  Wood'，N '  GreenWood'）; 

查看此链接： MSDN - 而不是触发器

hey guys
i use below trigger for get Date time in server

USE [DBStore]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

Create TRIGGER [dbo].[ServerTime] ON [dbo].[TblSellDocuments]
INSTEAD OF INSERT
AS
Begin Try
     SELECT * INTO #Inserted FROM Inserted
     UPDATE #Inserted SET Datein = GETDATE()
     INSERT INTO TblSellDocuments SELECT * FROM #Inserted

End Try
Begin Catch

End catch

but this doesn't work i have this error

An explicit value for the identity column in table 'TblSellDocuments' can only be specified when a column list is used and IDENTITY_INSERT is ON.

解决方案

an-explicit-value-for-the-identity-column-in-table-can-only-be-specified-when-a#2005527[^]

Check this link for your solution.

I would suggest you to read this article: Date and Time Functions (Transact-SQL)[^]. There are several ways to get server date and time.

You're using trigger to update DateIn field in Inserted table. You don't need to use trigger. You can use simple UPDATE[^] statement:

UPDATE Inserted SET Datein = GETDATE() --or other function

But if you would like to insert data from one table to another, use INSERT with SELECT:

INSERT INTO TblSellDocuments (Field1, Field2, DateTimeField)
SELECT Field1, Field2, GETDATE() AS DateTimeField
FROM Inserted

That's all!

The Error itself is Self Explanatory... It Says that u cannot Insert Identity columns in the base table for which IDENTITY INSERT is OFF.
If the INSTEAD OF INSERT view trigger generates an INSERT against the base table using the data in the inserted table, it must ignore the values for these types of columns(Identity columns,Columns With Time Stamp Data Type) by not including the columns in the select list of the INSERT statement. The INSERT statement can generate dummy values for these types of columns.

So i would suggest u to Use Set IDENTITY_INSERT ON or don't try to Insert values for Identity Columns like this..

--A correct INSERT statement that skips the ID and ComputedCol columns.
INSERT INTO BaseTable (Color, Material)
       VALUES (N'Red', N'Cloth');

--An incorrect statement that tries to supply a value for the 
--ID and ComputedCol columns.
INSERT INTO BaseTable
       VALUES (2, N'Green', N'Wood', N'GreenWood');

Check this Link: MSDN - Instead of Trigger

这篇关于使用触发器获取日期时间而不是客户日期时间的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！