January 16, 2017 at 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.
January 16, 2017 at 8:13 am
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
January 16, 2017 at 10:09 am
Senchi - Monday, January 16, 2017 8:02 AMALTER TRIGGER [dbo].[tr_datediff] ON [dbo] [JOURNAL] FOR INSERT,UPDATE
AS
SELECT DATEDIFF(d,start_date,end_date) [diff],[TimeDiff] FROM JOURNALstart_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.
January 16, 2017 at 1:14 pm
If you are trying to get just those rows updated or deleted, use this:
AS
SELECT DATEDIFF(d,start_date,end_date) [diff],[TimeDiff] FROM inserted
January 16, 2017 at 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.
January 16, 2017 at 2:00 pm
Senchi - Monday, January 16, 2017 1:40 PMI 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
January 16, 2017 at 4:05 pm
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
January 16, 2017 at 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.
January 16, 2017 at 10:11 pm
Steve Jones - SSC Editor - Monday, January 16, 2017 8:24 PMIf 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 insertedIf 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
January 16, 2017 at 10:36 pm
I have tried another way :
I have created a stored procedure [dbo].[sp_timedifference]
and in it :
[end_date])/60),':',
(DATEDIFF(Minute,start_date,[end_date])%60))
and a trigger with :
Is this OK ?
January 17, 2017 at 2:06 am
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.
January 17, 2017 at 2:07 am
Senchi - Monday, January 16, 2017 10:36 PMI have tried another way :
I have created a stored procedure [dbo].[sp_timedifference]
and in it :- update JOURNAL set DIFF=
[end_date])/60),':',
CONCAT((DATEDIFF(Minute,start_date,
(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.
January 17, 2017 at 2:21 am
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
January 17, 2017 at 8:15 am
I would follow David Burrows advice above. Join inserted to the original table in the trigger.
Viewing 15 posts - 1 through 15 (of 29 total)
You must be logged in to reply to this topic. Login to reply