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

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
tjgilbertAsked:
Who is Participating?
 
auke_tCommented:
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
 
gad_flyCommented:
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
 
imran_fastCommented:
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Aneesh RetnakaranDatabase AdministratorCommented:
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
 
tjgilbertAuthor Commented:
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
 
auke_tCommented:
That will not work correctly if multiple records are updated at once.
You need to use the INSERTED table to do that!
0
 
tjgilbertAuthor Commented:
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
 
auke_tCommented:
-- 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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.