Solved

Create Insert Trigger

Posted on 2011-09-30
5
221 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
[X]
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
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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

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. …
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

726 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