Solved

Create Insert Trigger

Posted on 2011-09-30
5
218 Views
Last Modified: 2012-05-12
I have the following table but need to create a timestamp trigger on the table.

CREATE TABLE Completion
(
      Recid int NOT NULL IDENTITY (1, 1) PRIMARY KEY,
      Application                  Varchar(60) NULL,
      Environment                  Varchar(4) NULL,
      Comment                        Varchar(max) NULL,
      CreationDate                    datetime NOT NULL
)


Can somebody help me with this?
0
Comment
Question by:Favorable
5 Comments
 
LVL 5

Assisted Solution

by:MrNetic
MrNetic earned 100 total points
ID: 36892648
0
 
LVL 21

Assisted Solution

by:JestersGrind
JestersGrind earned 100 total points
ID: 36892699
A timestamp is a special SQL Server data type that adds a sequential binary number to the column every time the records changes.  It's unique across the database.  For that you simply add a column of that data type.  No trigger required.  

I'm going to assume that you are referring to adding a date time trigger to your table, maybe populating the CreationDate column?  For that a trigger isn't necessary.  Just add a default of GetDate() to the CreationDate column.

If I'm completely off, please let me know and I'll suggest something else.

Greg

0
 
LVL 3

Accepted Solution

by:
pg_vinod earned 200 total points
ID: 36892759
Are you looking for timestamp to update "CreationDate" column?
If it on insert then you can create default Constraint as well on column level of the table.
CREATE TABLE Completion
(
      Recid int NOT NULL IDENTITY (1, 1) PRIMARY KEY,
      Application                  Varchar(60) NULL,
      Environment                  Varchar(4) NULL,
      Comment                        Varchar(max) NULL,
      CreationDate                    datetime default current_timestamp NOT NULL
)


0
 
LVL 8

Assisted Solution

by:Leo Torres
Leo Torres earned 100 total points
ID: 36893123
CREATE TABLE Completion
(
      Recid int NOT NULL IDENTITY (1, 1) PRIMARY KEY,
      Application                  Varchar(60) NULL,
      Environment                  Varchar(4) NULL,
      Comment                        Varchar(max) NULL,
      CreationDate                    datetime default current_timestamp NOT NULL
)

or
in SSMS right click on table--> click design--> click on column--_ at the bottom pannel find defualt and enter getdate() there
this will work for an existing table
0
 

Author Closing Comment

by:Favorable
ID: 36913797
My very thank you goes to everyone.
0

Featured Post

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL 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.

937 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

Need Help in Real-Time?

Connect with top rated Experts

4 Experts available now in Live!

Get 1:1 Help Now