January 20, 2017 at 4:10 am
David Burrows - Tuesday, January 17, 2017 2:06 AM1. 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
I have tried your way but am getting :
Ambiguous column name 'start_date'
Ambiguous column name 'end_date'
.....
January 20, 2017 at 4:19 am
Senchi - Friday, January 20, 2017 4:10 AMI have tried your way but am getting :
Ambiguous column name 'start_date'
Ambiguous column name 'end_date'
.....
Error message gives you your answer really. The poster didn't declare which table those fields are coming from. Add the table alias, and your problem is solved.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
January 20, 2017 at 4:33 am
Thom A - Friday, January 20, 2017 4:19 AMSenchi - Friday, January 20, 2017 4:10 AMI have tried your way but am getting :
Ambiguous column name 'start_date'
Ambiguous column name 'end_date'
.....Error message gives you your answer really. The poster didn't declare which table those fields are coming from. Add the table alias, and your problem is solved.
Sorry Thom, just a beginner... 🙂
Mind telling me how and where?
January 20, 2017 at 4:51 am
Senchi - Friday, January 20, 2017 4:33 AMSorry Thom, just a beginner... 🙂
Mind telling me how and where?
I'll direct you, and see if you can solve it yourself.
In this example, there are two alias' i and j, representing the tables inserted and JOURNAL.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
The error message has told you which fields are you problem, start_date, and end_date (they appera in both tables), however, I have but them in bold and italics above. you can see how the aliases are used in the join clause, which i have underlined. Have a go at resolving this yourself, and let me know if you get stuck 🙂
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
January 20, 2017 at 5:07 am
Thom A - Friday, January 20, 2017 4:51 AMSenchi - Friday, January 20, 2017 4:33 AMSorry Thom, just a beginner... 🙂
Mind telling me how and where?I'll direct you, and see if you can solve it yourself.
In this example, there are two alias' i and j, representing the tables inserted and JOURNAL.
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
The error message has told you which fields are you problem, start_date, and end_date (they appera in both tables), however, I have but them in bold and italics above. you can see how the aliases are used in the join clause, which i have underlined. Have a go at resolving this yourself, and let me know if you get stuck 🙂
I get UPDATE j = JOIN JOURNAL
so j is my journal table.
ON j.ID = i.ID (so inserted , they are joined on primary key ID)
So my trigger will update only the newly inserted records.
what I cant figure out is setting the aliases for the start_date,end_date and why are they needed in the first place
since fields exist .
also why use 'j' ? Could I not just use :
update JOURNAL
set ....
FROM dbo.JOURNAL
INNER JOIN inserted ON JOURNAL.ID = inserted.ID
END
January 20, 2017 at 5:16 am
The problem is they exist in both tables. If I gave you 2 apples, one red, which you place in your left hand, and one green which you place in your right hand, and I asked you ""hat colour is the apple", which colour would you give? I might want to know the colour of the apple in your left hand (red), but you have know way of knowing. If I asked you, what colour is the apple in your left hand, you can safely answer "red".
The same is true here. the fields start_Date and End_date exist in both the tables JOURNAL and inserted. If you ask SQL to give you the value of start_date it doesn't know what table to look at, so it presents an error.
The alias for your inserted table is i, so this is the lias you need here (for example, i.start_date). Make sense?
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
January 20, 2017 at 5:33 am
Thom A - Friday, January 20, 2017 5:16 AMThe problem is they exist in both tables. If I gave you 2 apples, one red, which you place in your left hand, and one green which you place in your right hand, and I asked you ""hat colour is the apple", which colour would you give? I might want to know the colour of the apple in your left hand (red), but you have know way of knowing. If I asked you, what colour is the apple in your left hand, you can safely answer "red".The same is true here. the fields start_Date and End_date exist in both the tables JOURNAL and inserted. If you ask SQL to give you the value of start_date it doesn't know what table to look at, so it presents an error.
The alias for your inserted table is i, so this is the lias you need here (for example, i.start_date). Make sense?
ALTER TRIGGER [dbo].[tr_datediff] ON [dbo][JOURNAL] FOR INSERT,UPDATE
AS
UPDATE j
SET DIFF = CONCAT((DATEDIFF(Minute,i.start_date,[i.end_date])/60),':',
(DATEDIFF(Minute,i.start_date,[i.end_date])%60))
FROM INSERTED i
JOIN JOURNAL j ON j.key = i.key
gives me
ERROR - The row value(s) updated or deleted either do not make the row unique or they alter multiple rows.
January 20, 2017 at 7:03 am
Senchi - Friday, January 20, 2017 5:33 AMThom A - Friday, January 20, 2017 5:16 AMThe problem is they exist in both tables. If I gave you 2 apples, one red, which you place in your left hand, and one green which you place in your right hand, and I asked you ""hat colour is the apple", which colour would you give? I might want to know the colour of the apple in your left hand (red), but you have know way of knowing. If I asked you, what colour is the apple in your left hand, you can safely answer "red".The same is true here. the fields start_Date and End_date exist in both the tables JOURNAL and inserted. If you ask SQL to give you the value of start_date it doesn't know what table to look at, so it presents an error.
The alias for your inserted table is i, so this is the lias you need here (for example, i.start_date). Make sense?
ALTER TRIGGER [dbo].[tr_datediff] ON [dbo][JOURNAL] FOR INSERT,UPDATE
AS
UPDATE j
SET DIFF = CONCAT((DATEDIFF(Minute,i.start_date,[i.end_date])/60),':',
(DATEDIFF(Minute,i.start_date,[i.end_date])%60))
FROM INSERTED i
JOIN JOURNAL j ON j.key = i.key
gives me
ERROR - The row value(s) updated or deleted either do not make the row unique or they alter multiple rows.
CREATE TRIGGER [dbo].[tr_datediff] ON [dbo].[JOURNAL]
FOR INSERT,UPDATE
AS
UPDATE j
SET DIFF = CONCAT((DATEDIFF(Minute,i.[start_date],i.end_date)/60),':',
(DATEDIFF(Minute,i.[start_date],i.end_date)%60))
FROM INSERTED i
JOIN JOURNAL j ON j. = i.
This worked for me.
What is the name of the unique key on the JOURNAL table
Far away is close at hand in the images of elsewhere.
Anon.
January 20, 2017 at 7:05 am
Senchi - Friday, January 20, 2017 5:33 AMThom A - Friday, January 20, 2017 5:16 AMThe problem is they exist in both tables. If I gave you 2 apples, one red, which you place in your left hand, and one green which you place in your right hand, and I asked you ""hat colour is the apple", which colour would you give? I might want to know the colour of the apple in your left hand (red), but you have know way of knowing. If I asked you, what colour is the apple in your left hand, you can safely answer "red".The same is true here. the fields start_Date and End_date exist in both the tables JOURNAL and inserted. If you ask SQL to give you the value of start_date it doesn't know what table to look at, so it presents an error.
The alias for your inserted table is i, so this is the lias you need here (for example, i.start_date). Make sense?
ALTER TRIGGER [dbo].[tr_datediff] ON [dbo][JOURNAL] FOR INSERT,UPDATE
AS
UPDATE j
SET DIFF = CONCAT((DATEDIFF(Minute,i.start_date,[i.end_date])/60),':',
(DATEDIFF(Minute,i.start_date,[i.end_date])%60))
FROM INSERTED i
JOIN JOURNAL j ON j.key = i.key
gives me
ERROR - The row value(s) updated or deleted either do not make the row unique or they alter multiple rows.
What SQL are you running to get this returned?
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
January 20, 2017 at 7:18 am
Thom A - Friday, January 20, 2017 7:05 AMSenchi - Friday, January 20, 2017 5:33 AMThom A - Friday, January 20, 2017 5:16 AMThe problem is they exist in both tables. If I gave you 2 apples, one red, which you place in your left hand, and one green which you place in your right hand, and I asked you ""hat colour is the apple", which colour would you give? I might want to know the colour of the apple in your left hand (red), but you have know way of knowing. If I asked you, what colour is the apple in your left hand, you can safely answer "red".The same is true here. the fields start_Date and End_date exist in both the tables JOURNAL and inserted. If you ask SQL to give you the value of start_date it doesn't know what table to look at, so it presents an error.
The alias for your inserted table is i, so this is the lias you need here (for example, i.start_date). Make sense?
ALTER TRIGGER [dbo].[tr_datediff] ON [dbo][JOURNAL] FOR INSERT,UPDATE
AS
UPDATE j
SET DIFF = CONCAT((DATEDIFF(Minute,i.start_date,[i.end_date])/60),':',
(DATEDIFF(Minute,i.start_date,[i.end_date])%60))
FROM INSERTED i
JOIN JOURNAL j ON j.key = i.key
gives me
ERROR - The row value(s) updated or deleted either do not make the row unique or they alter multiple rows.What SQL are you running to get this returned?
It's because he's put the alias inside the square brackets. The square brackets aren't actually needed - they don't enclose any special characters or reserved keywords. If he insists on keeping them, the alias needs to go outside, otherwise SQL Server will treat i.end_date as the name of a column.
John
January 20, 2017 at 7:33 am
John Mitchell-245523 - Friday, January 20, 2017 7:18 AMThom A - Friday, January 20, 2017 7:05 AMSenchi - Friday, January 20, 2017 5:33 AMThom A - Friday, January 20, 2017 5:16 AMThe problem is they exist in both tables. If I gave you 2 apples, one red, which you place in your left hand, and one green which you place in your right hand, and I asked you ""hat colour is the apple", which colour would you give? I might want to know the colour of the apple in your left hand (red), but you have know way of knowing. If I asked you, what colour is the apple in your left hand, you can safely answer "red".The same is true here. the fields start_Date and End_date exist in both the tables JOURNAL and inserted. If you ask SQL to give you the value of start_date it doesn't know what table to look at, so it presents an error.
The alias for your inserted table is i, so this is the lias you need here (for example, i.start_date). Make sense?
ALTER TRIGGER [dbo].[tr_datediff] ON [dbo][JOURNAL] FOR INSERT,UPDATE
AS
UPDATE j
SET DIFF = CONCAT((DATEDIFF(Minute,i.start_date,[i.end_date])/60),':',
(DATEDIFF(Minute,i.start_date,[i.end_date])%60))
FROM INSERTED i
JOIN JOURNAL j ON j.key = i.key
gives me
ERROR - The row value(s) updated or deleted either do not make the row unique or they alter multiple rows.What SQL are you running to get this returned?
It's because he's put the alias inside the square brackets. The square brackets aren't actually needed - they don't enclose any special characters or reserved keywords. If he insists on keeping them, the alias needs to go outside, otherwise SQL Server will treat i.end_date as the name of a column.
John
This is sql server 2014 express
even if I remove brackets I still get the same error.
January 20, 2017 at 7:58 am
Senchi - Friday, January 20, 2017 7:33 AMJohn Mitchell-245523 - Friday, January 20, 2017 7:18 AMThom A - Friday, January 20, 2017 7:05 AMSenchi - Friday, January 20, 2017 5:33 AMThom A - Friday, January 20, 2017 5:16 AMThe problem is they exist in both tables. If I gave you 2 apples, one red, which you place in your left hand, and one green which you place in your right hand, and I asked you ""hat colour is the apple", which colour would you give? I might want to know the colour of the apple in your left hand (red), but you have know way of knowing. If I asked you, what colour is the apple in your left hand, you can safely answer "red".The same is true here. the fields start_Date and End_date exist in both the tables JOURNAL and inserted. If you ask SQL to give you the value of start_date it doesn't know what table to look at, so it presents an error.
The alias for your inserted table is i, so this is the lias you need here (for example, i.start_date). Make sense?
ALTER TRIGGER [dbo].[tr_datediff] ON [dbo][JOURNAL] FOR INSERT,UPDATE
AS
UPDATE j
SET DIFF = CONCAT((DATEDIFF(Minute,i.start_date,[i.end_date])/60),':',
(DATEDIFF(Minute,i.start_date,[i.end_date])%60))
FROM INSERTED i
JOIN JOURNAL j ON j.key = i.key
gives me
ERROR - The row value(s) updated or deleted either do not make the row unique or they alter multiple rows.What SQL are you running to get this returned?
It's because he's put the alias inside the square brackets. The square brackets aren't actually needed - they don't enclose any special characters or reserved keywords. If he insists on keeping them, the alias needs to go outside, otherwise SQL Server will treat i.end_date as the name of a column.
John
This is sql server 2014 express
even if I remove brackets I still get the same error.
OK. I imagine this is because your inserted rows have the same value of key repeated. When the join is done, j.key matches with more than one row in Inserted. Therefore SQL Server doesn't know which of those rows to take the values of i.start_date and i.end_date from. I'm pleasantly surprised that you get an error here - I thought the UPDATE...FROM syntax allowed stuff like that to go unreported. Please will you post full DDL for the JOURNAL table? I'm interested in particular in whether key is unique. If it is unique then your trigger is perhaps updating the value of key to a value that already exists in the column.
January 20, 2017 at 8:12 am
Senchi - Friday, January 20, 2017 7:33 AMThis is sql server 2014 express
even if I remove brackets I still get the same error.
As John said, sounds like you have bad data. This works on SQL 2014 Express:
USE DevDB;
GO
CREATE TABLE JOURNAL ([Key] INT IDENTITY(1,1), start_date datetime, end_date datetime, diff VARCHAR(20));
GO
CREATE TRIGGER [dbo].[tr_datediff] ON [dbo].[JOURNAL] FOR INSERT,UPDATE
AS
UPDATE j
SET DIFF = CONCAT((DATEDIFF(Minute,i.start_date,i.end_date)/60),':',
(DATEDIFF(Minute,i.start_date,i.end_date)%60))
FROM INSERTED i
LEFT JOIN JOURNAL j ON j. = i.;
GO
INSERT INTO JOURNAL (start_date, end_date)
VALUES ('20-Jan-2017 15:00:00.000', GETDATE());
SELECT *
FROM JOURNAL;
GO
UPDATE JOURNAL
SET start_date = '20-Jan-2017 14:00:00.000'
WHERE [Key] = 1;
GO
SELECT *
FROM JOURNAL;
GO
DROP TABLE JOURNAL;
Returns the following:Key start_date end_date diff
----------- ----------------------- ----------------------- --------------------
1 2017-01-20 15:00:00.000 2017-01-20 15:11:29.367 0:11
Key start_date end_date diff
----------- ----------------------- ----------------------- --------------------
1 2017-01-20 14:00:00.000 2017-01-20 15:11:29.367 1:11
The trigger isn't the problem any more, it's your data.
Edit: looks like plain text formatting has gone down the pan. Well F...
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
January 20, 2017 at 9:52 am
January 20, 2017 at 10:23 am
I would urge you to test this with a bunch of data sets and be sure it works as you expect. Left (or right) joins can cause issues at times.
Viewing 15 posts - 16 through 29 (of 29 total)
You must be logged in to reply to this topic. Login to reply