asked on

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

gad_fly

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

imran_fast

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

Aneesh

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

ASKER CERTIFIED SOLUTION

auke_t

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

tjgilbert

ASKER

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

auke_t

That will not work correctly if multiple records are updated at once.
You need to use the INSERTED table to do that!

tjgilbert

ASKER

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

auke_t

-- 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