?
Solved

Create Insert Trigger

Posted on 2011-09-30
5
Medium Priority
?
224 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 400 total points
ID: 36892648
0
 
LVL 21

Assisted Solution

by:JestersGrind
JestersGrind earned 400 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 800 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 400 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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

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. …
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
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.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Suggested Courses

770 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