Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Create Insert Trigger

Posted on 2011-09-30
5
220 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

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Database ERD 4 26
Stored Proc - Rewrite 42 56
Help with Oracle IF statment 5 23
SQL Availablity Groups Shared Path 2 13
I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how the fundamental information of how to create a table.

809 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