why is this trigger not working ?

Question

Post reply

why is this trigger not working ?

Senchi

Ten Centuries

Points: 1184
More actions
January 16, 2017 at 8:02 am

#333970

ALTER TRIGGER [dbo].[tr_datediff] ON [dbo] [JOURNAL] FOR INSERT,UPDATE AS SELECT DATEDIFF(d,start_date,end_date) [diff],[TimeDiff] FROM JOURNAL
start_date is datetime
end_date is datetime
TimeDiff is varchar(50)
And yet it does not work.

update JOURNAL set DIFF=
CONCAT((DATEDIFF(Minute,start_date,

[end_date])/60),':',
(DATEDIFF(Minute,start_date,[end_date])%60))

and a trigger with :

[dbo].[trg_timediff] on [dbo].[JOURNAL]
for insert,update
as
exec sp_timedifference;

Is this OK ?

Thom A SSC Guru Points: 99143 More actions · Answer 1

Apart from that your ALTER statement is missing a period, it should be ON [dbo].[JOURNAL], I can't see anything wrong with it.

What error are you getting?

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 2

Please will you explain the purpose of the trigger? How many rows are in the table? All it's going to do is return the number of days between start_date and end_date and the value of TimeDiff for every row in the table, every time you do an insert or update on the table.

And yes, saying it doesn't work is a bit like going to the doctor and saying you're ill.

John

Chris Harshman SSC-Forever Points: 42192 More actions · Answer 3

Senchi - Monday, January 16, 2017 8:02 AM
ALTER TRIGGER [dbo].[tr_datediff] ON [dbo] [JOURNAL] FOR INSERT,UPDATE AS SELECT DATEDIFF(d,start_date,end_date) [diff],[TimeDiff] FROM JOURNAL
start_date is datetime
end_date is datetime
TimeDiff is varchar(50)
And yet it does not work.

What are you doing with the results of this DATEDIFF? Are you really trying to return results from the trigger? I didn't think this was supported anymore:
https://technet.microsoft.com/en-us/library/ms186337(v=sql.110).aspx
You could store it in a variable and do something else with it after that maybe, it depends what you are going to use the value returned for.

Steve Jones - SSC Editor SSC Guru Points: 738337 More actions · Answer 4

If you are trying to get just those rows updated or deleted, use this:


ALTER TRIGGER [dbo].[tr_datediff] ON [dbo] [JOURNAL]  FOR INSERT,UPDATE
AS
SELECT DATEDIFF(d,start_date,end_date) [diff],[TimeDiff] FROM inserted

Senchi Ten Centuries Points: 1184 More actions · Answer 5

I am actually just updating the end_date field. So when it gets written (updated) I wanted the trigger to calculate the time difference.
So, yes...basically I want a result from trigger.

Thom A SSC Guru Points: 99143 More actions · Answer 6

Senchi - Monday, January 16, 2017 1:40 PM
I am actually just updating the end_date field. So when it gets written (updated) I wanted the trigger to calculate the time difference.
So, yes...basically I want a result from trigger.

You want a result, or you do you want to update the result set so that a column has a particular value?

As Chris pointed out, Returning a Resultset in a trigger is deprecated, so you're better finding a different solution if you are somehow making use of the resultset returned upon update (like a separate SELECT statement).

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

Jeffrey Williams SSC Guru Points: 90349 More actions · Answer 7

If you are looking for results from the insert - you could use the OUTPUT clause on the INSERT/UPDATE statement itself instead of using a trigger.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

Steve Jones - SSC Editor SSC Guru Points: 738337 More actions · Answer 8

If you're updating, then why not put the UPDATE in the trigger? Something like this:

ALTER TRIGGER [dbo].[tr_datediff] ON [dbo][JOURNAL] FOR INSERT,UPDATE AS Update mytable set mycol = DATEDIFF(d,start_date,end_date)[diff] FROM inserted

If the column to be updated is in the table, then join back to the Journal table with inserted to find the correct row.

Senchi Ten Centuries Points: 1184 More actions · Answer 9

Steve Jones - SSC Editor - Monday, January 16, 2017 8:24 PM
If you're updating, then why not put the UPDATE in the trigger? Something like this:
ALTER TRIGGER [dbo].[tr_datediff] ON [dbo][JOURNAL] FOR INSERT,UPDATE AS Update mytable set mycol = DATEDIFF(d,start_date,end_date)[diff] FROM inserted
If the column to be updated is in the table, then join back to the Journal table with inserted to find the correct row.

I am getting the error :
Ambiguous column name start_date .....
Ambiguous column name end_date

Senchi Ten Centuries Points: 1184 More actions · Answer 10

I have tried another way :
I have created a stored procedure [dbo].[sp_timedifference]
and in it :

David Burrows SSC Guru Points: 65144 More actions · Answer 11

1. The update should be in the trigger there is no need for an additional procedure
2. The UPDATE statement will update every row in the JOURNAL table not just the ones updated
3. Triggers have a special table called INSERTED which contains the row(s) after any updates applied
   NOTE that this table can have many rows
4. It is normal to join INSERTED to JOURNAL to apply any other updates
   e.g.
ALTER TRIGGER [dbo].[tr_datediff] ON [dbo][JOURNAL] FOR INSERT,UPDATE AS UPDATE j SET DIFF = CONCAT((DATEDIFF(Minute,start_date,[end_date])/60),':',              (DATEDIFF(Minute,start_date,[end_date])%60)) FROM INSERTED i JOIN JOURNAL j ON j.key = i.key

Far away is close at hand in the images of elsewhere.
Anon.

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 12

Senchi - Monday, January 16, 2017 10:36 PM
I have tried another way :
I have created a stored procedure [dbo].[sp_timedifference]
and in it :
update JOURNAL set DIFF=
CONCAT((DATEDIFF(Minute,start_date,
[end_date])/60),':',
(DATEDIFF(Minute,start_date,[end_date])%60))
and a trigger with :
[dbo].[trg_timediff] on [dbo].[JOURNAL]
for insert,update
as
exec sp_timedifference;
Is this OK ?

Probably not. For a start, it's updating the whole table, not just the inserted/updated row(s). Second, is concatenating the minute portion of the start and end dates into the same column really helpful? The name of the column suggests to me that you want the number of minutes elapsed, in which case you should use the DATEDIFF function.

John

Edit - oops, ignore my "Second" point - I didn't read your code properly. Apologies.

Thom A SSC Guru Points: 99143 More actions · Answer 13

Could you not just have the value as a computed column, then there is no need to update, as it's calculated? For example:
USE DevTestDB; GO

CREATE TABLE Dates (ID INT IDENTITY(1,1), Start_Date DATETIME, End_Date DATETIME, DIFF AS (DATEDIFF(MINUTE,Start_date,End_Date))); GO

INSERT INTO Dates (Start_Date, End_Date) VALUES ('15-Jan-2016 19:19:19.000', '15-Jan-2016 20:20:20.000'), ('16-Jan-2016 10:10:00.000', '16-Jan-2016 16:45:22.000'); GO

SELECT * FROM Dates;

UPDATE Dates SET End_Date = '16-Jan-2016 12:17:22.000' WHERE ID = 2;

SELECT * FROM Dates;

GO DROP TABLE Dates

This brings back the following respectively:
ID Start_Date End_Date DIFF ----------- ----------------------- ----------------------- ----------- 1 2016-01-15 19:19:19.000 2016-01-15 20:20:20.000 61 2 2016-01-16 10:10:00.000 2016-01-16 16:45:22.000 395

(2 row(s) affected)

ID Start_Date End_Date DIFF ----------- ----------------------- ----------------------- ----------- 1 2016-01-15 19:19:19.000 2016-01-15 20:20:20.000 61 2 2016-01-16 10:10:00.000 2016-01-16 12:17:22.000 127

(2 row(s) affected)

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

Steve Jones - SSC Editor SSC Guru Points: 738337 More actions · Answer 14

I would follow David Burrows advice above. Join inserted to the original table in the trigger.

why is this trigger not working ?

Cookies on SQLServerCentral