Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Trigger on record insert

Posted on 2009-12-18
14
Medium Priority
?
285 Views
Last Modified: 2012-05-08
Hello,

I want to write a trigger where as soon as a record has been inserted into the tablei have a field called 'DateAddedToDB' which i want to update with the day the record was added.

Im not familiar with triggers so im not really sure how to tackle it... any suggestions.

Thanks,
0
Comment
Question by:AIdoHSG
  • 5
  • 5
  • 4
14 Comments
 
LVL 35

Expert Comment

by:YZlat
ID: 26082878
is this field in the table  to which you insert data?
0
 

Author Comment

by:AIdoHSG
ID: 26082894
yes it is in the same table im inserting the data. Also either i will sometimes have one entry or i might upload a list.

Thanks,
0
 
LVL 35

Expert Comment

by:YZlat
ID: 26082915
CREATE TRIGGER trg_RecordDate

ON Table1

FOR INSERT

AS

UPDATE Table1 SET DateAddedToDB=GETDATE() WHERE ...
0
Get quick recovery of individual SharePoint items

Free tool – Veeam Explorer for Microsoft SharePoint, enables fast, easy restores of SharePoint sites, documents, libraries and lists — all with no agents to manage and no additional licenses to buy.

 

Author Comment

by:AIdoHSG
ID: 26082927
what would be the where condition for getting the new record?
0
 
LVL 35

Expert Comment

by:YZlat
ID: 26082934

One question  though - Why do you need a trigger if the field to be updated is in the same table on which you perform insert???

Why not just do a simple INSERT?
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 26082942
You do not need a WHERE clause, but rather make use of the INSERTED logical table.
0
 

Author Comment

by:AIdoHSG
ID: 26082948
sometimes i wont be entering data... it is by other users and it is done through a form... so when data is entered i'm not running a sql insert query.
0
 

Author Comment

by:AIdoHSG
ID: 26082958
acperkins what is the INSERTED logical table? not sure what that is

thx
0
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 1000 total points
ID: 26082982
Assuming that DateAddedToDB is in the same table then the best way to solve this problem is simply to add a DEFAULT value of GETDATE() to the DateAddedToDB.
0
 
LVL 35

Expert Comment

by:YZlat
ID: 26082991
can you post the table structure?

If the date field is in each row you can simply insert the date with the rest of the data
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 26083011
If you still want to use a TRIGGER than do it this way:
CREATE TRIGGER trg_YourTriggerNameGoesHere ON YourTableNameGoesHere

AFTER INSERT

AS

BEGIN

UPDATE t
SET      DateAddedToDB = GETDATE()
FROM      YourTableNameGoesHere t
      INNER JOIN INSERTED i ON t.<yourprimarykeygoeshere> = i.<yourprimarykeygoeshere>
END
0
 

Author Closing Comment

by:AIdoHSG
ID: 31667892
Actually that worked...
Thanks... that was simple...
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 26083019
>>If the date field is in each row you can simply insert the date with the rest of the data<<
But that is the point, the author is saying they have no control over that, hence it has to be using a DEFAULT value (simplest) or a TRIGGER.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 26083033
>> what is the INSERTED logical table?<<
If in future you ever need to use a TRIGGER make sure to read up on the INSERTED and DELETED logical tables.  You are going to need them.
0

Featured Post

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Suggested Courses

578 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question