?
Solved

MS SQL triggers.  How to over ride a trigger and add a date.

Posted on 2006-04-06
8
Medium Priority
?
225 Views
Last Modified: 2006-11-18
I have a trigger set on the Date feild in the Action Table.  I need to be able to add a different date if the user supplies one.  How can I still keep this trigger?  Or do I have to remove it and add the current date manually every time.


CREATE TRIGGER [Action Date] ON [db129981905].[dbo129981905].[Actions]
FOR INSERT, UPDATE
AS
    SET DATEFORMAT mdy
     update Actions
     Set ActionDate = GetDate()

Where ActionID = @@Identity
0
Comment
Question by:tjgilbert
8 Comments
 
LVL 2

Expert Comment

by:gad_fly
ID: 16396757
Hi

it's better to remove Update from your trigger . and now you can update it with what ever you want .

CREATE TRIGGER [Action Date] ON [db129981905].[dbo129981905].[Actions]
FOR INSERT
AS
    SET DATEFORMAT mdy
     update Actions
     Set ActionDate = GetDate()

Where ActionID = @@Identity


Mohammad Pourebtehaj
0
 
LVL 28

Expert Comment

by:imran_fast
ID: 16396759
why wont you use default value for the field actiondate?
alter table action alter column actiondate datetime default (getdate())
any how if you want to modify trigger then

CREATE TRIGGER [Action Date] ON [db129981905].[dbo129981905].[Actions]
FOR INSERT, UPDATE
AS
    SET DATEFORMAT mdy
if exists(select * from actions with(nolock) where actionid = @@identity and actiondate is null )

     update Actions
     Set ActionDate = GetDate()

Where ActionID = @@Identity
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 16397389
CREATE TRIGGER [Action Date] ON [db129981905].[dbo129981905].[Actions]
FOR INSERT, UPDATE
AS
    SET DATEFORMAT mdy
    update Actions
    Set ActionDate = GetDate()
    FROM [db129981905].[dbo129981905].[Actions] inner join inserted ins
    ON Actions.ActionID = ins.ActionID
0
NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

 
LVL 9

Accepted Solution

by:
auke_t earned 750 total points
ID: 16399635
CREATE TRIGGER [Action Date] ON [db129981905].[dbo129981905].[Actions]
FOR INSERT, UPDATE
AS
     update Actions
     Set ActionDate = IsNull(ActionDate, GetDate())
     FROM INSERTED
     Where Actions.ActionID = INSERTED.ActionID
0
 

Author Comment

by:tjgilbert
ID: 16556899
Thank you for your help this is what I ended up using:

CREATE TRIGGER [Action Date] ON [db129981905].[dbo129981905].[Actions]
FOR INSERT, UPDATE
AS
    SET DATEFORMAT mdy
     update Actions
    Set ActionDate = IsNull(ActionDate, GetDate())

Where ActionID = @@Identity
0
 
LVL 9

Expert Comment

by:auke_t
ID: 16594026
That will not work correctly if multiple records are updated at once.
You need to use the INSERTED table to do that!
0
 

Author Comment

by:tjgilbert
ID: 16635040
I get an Error 209 : Ambigous column named 'ActionDate'

CREATE TRIGGER [Action Date] ON [db129981905].[dbo129981905].[Actions]
FOR INSERT, UPDATE
AS
    SET DATEFORMAT mdy
     update Actions

     Set ActionDate = IsNull(ActionDate, GetDate())
     FROM INSERTED
     Where Actions.ActionID = INSERTED.ActionID
0
 
LVL 9

Expert Comment

by:auke_t
ID: 16636751
-- Ok this should work!

CREATE TRIGGER [Action Date] ON [db129981905].[dbo129981905].[Actions]
FOR INSERT, UPDATE
AS

SET NOCOUNT ON

update
    Actions
Set
    ActionDate = IsNull(INSERTED.ActionDate, GetDate())
FROM
    INSERTED
Where
    Actions.ActionID = INSERTED.ActionID
0

Featured Post

Granular recovery for Microsoft Exchange

With Veeam Explorer for Microsoft Exchange you can choose the Exchange Servers and restore points you’re interested in, and Veeam Explorer will present the contents of those mailbox stores for browsing, searching and exporting.

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…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
Suggested Courses

862 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