Click here to Skip to main content
15,886,362 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
hey guys
i use below trigger for get Date time in server

SQL
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
SQL
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.
Posted

 
Share this answer
 
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:
SQL
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:
SQL
INSERT INTO TblSellDocuments (Field1, Field2, DateTimeField)
SELECT Field1, Field2, GETDATE() AS DateTimeField
FROM Inserted


That's all!
 
Share this answer
 
v2
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..

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

SQL
--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
 
Share this answer
 
v2
Comments
Adarsh chauhan 31-Jul-13 7:55am    
Exact and To the point explanation. +5
Raja Sekhar S 31-Jul-13 7:59am    
Thank you Adarsh...
when i have Identity field i have to wire field name instead '*'


SQL
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 sumprice,name FROM #Inserted
 
End Try
Begin Catch
 
End catch
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900