April 6, 2018 at 7:57 am
Hello all,
I have a table:CREATE TABLE [dbo].[udLaborTracking](
[Co] NOT NULL,
[Employee] NULL,
[Job] NOT NULL,
[LastJob] NULL,
[Notes] [varchar](max) NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
When anyone changes (updates) the [Job] field, I'd like to move the value, before it is changed, in to the [LastJob] field. So I can see what the last job they were on actually was.
Also, can I put triggers on Views?
Thank you for your help,
April 6, 2018 at 9:14 am
You can create a trigger on a view. Here's the documentation.
Instead of a trigger, I'd suggest a design that let's you add jobs. What if they have more than two? Normalize it to at least two tables to make this work.
However, a trigger would work something like this:
CREATE TRIGGER LastJob
ON dbo.udLaborTracking
AFTER UPDATE
AS
UPDATE dbo.udLaborTracking
SET LastJob = d.Job
FROM dbo.udLaborTracking AS ult
JOIN deleted AS d
ON d.Co = ult.Co;
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 6, 2018 at 9:59 am
I'm not sure I'm following. The Job and LastJob are not keys. The primary Keys are Company and Employee
There will always be only one Employee (it can never be repeated). Each employee record will then have one Job and possibly one LastJob.
April 6, 2018 at 10:10 am
Ken at work - Friday, April 6, 2018 9:59 AMI'm not sure I'm following. The Job and LastJob are not keys. The primary Keys are Company and Employee
There will always be only one Employee (it can never be repeated). Each employee record will then have one Job and possibly one LastJob.
So, change the code. Notice your example table up there. It doesn't have a single primary key (or data types). I had to guess. I guessed wrong, fine, just make the adjustments.
As to only one employee, you're missing the point. Sure, there only one employee, but how many jobs can they possibly have? Infinite really. You're structure limits them to what they have now and what they had before. No history is possible. By normalizing this structure, you can store three, four, however many previous jobs there were.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 6, 2018 at 10:29 am
Grant Fritchey - Friday, April 6, 2018 10:10 AMKen at work - Friday, April 6, 2018 9:59 AMI'm not sure I'm following. The Job and LastJob are not keys. The primary Keys are Company and Employee
There will always be only one Employee (it can never be repeated). Each employee record will then have one Job and possibly one LastJob.So, change the code. Notice your example table up there. It doesn't have a single primary key (or data types). I had to guess. I guessed wrong, fine, just make the adjustments.
As to only one employee, you're missing the point. Sure, there only one employee, but how many jobs can they possibly have? Infinite really. You're structure limits them to what they have now and what they had before. No history is possible. By normalizing this structure, you can store three, four, however many previous jobs there were.
I'm sorry I haven't been clear. Really there is no way for an employee to have more than one job and one last job.
April 9, 2018 at 6:27 am
Ken at work - Friday, April 6, 2018 10:29 AMGrant Fritchey - Friday, April 6, 2018 10:10 AMKen at work - Friday, April 6, 2018 9:59 AMI'm not sure I'm following. The Job and LastJob are not keys. The primary Keys are Company and Employee
There will always be only one Employee (it can never be repeated). Each employee record will then have one Job and possibly one LastJob.So, change the code. Notice your example table up there. It doesn't have a single primary key (or data types). I had to guess. I guessed wrong, fine, just make the adjustments.
As to only one employee, you're missing the point. Sure, there only one employee, but how many jobs can they possibly have? Infinite really. You're structure limits them to what they have now and what they had before. No history is possible. By normalizing this structure, you can store three, four, however many previous jobs there were.
I'm sorry I haven't been clear. Really there is no way for an employee to have more than one job and one last job.
Unfortunately, you missed his point entirely. The structure you have only allows for the current job and most recent previous job to be kept track of. If you'll NEVER EVER track any further history than the very last job someone had, then perhaps it doesn't matter, but data structures of that nature have their own set of difficulties that the alternative solves in a much more performant and scalable way. Why NOT be able to track the entire history of jobs that a given person could ever have?
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
April 9, 2018 at 3:39 pm
SET ANSI_NULLS ON;
SET QUOTED_IDENTIFIER ON;
GO
CREATE TRIGGER udLaborTracking__TRG_UPD_Set_LastJob
ON dbo.udLaborTracking
AFTER UPDATE
AS
SET NOCOUNT ON;
IF UPDATE(Job)
BEGIN
UPDATE ult /*this is critical: update the alias name, not the original table name*/
SET LastJob = d.Job
FROM dbo.udLaborTracking AS ult
INNER JOIN deleted AS d ON d.Co = ult.Co AND d.Employee = ult.Employee
WHERE ult.Job <> d.Job OR d.Job IS NULL
END /*IF*/
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
April 10, 2018 at 12:22 pm
Thank you, I think I'm going to give this a try.
I was curious, not being as familiar with the coding, why does the update need to address the alias as opposed to the actual table?
April 10, 2018 at 12:31 pm
Ken at work - Tuesday, April 10, 2018 12:22 PMThank you, I think I'm going to give this a try.
I was curious, not being as familiar with the coding, why does the update need to address the alias as opposed to the actual table?
Because once you've an alias for a table in a query, you must use that alias, and only that aliast, to refer to that table from then on. The same table reference/"rendering" cannot have two different names in a query, ever.
--For example:
SELECT TOP (10) sys.objects.* FROM sys.objects /*should work fine*/
SELECT TOP (10) o.* FROM sys.objects o /*should work fine*/
/*should NOT work at all, because the name "sys.objects" has been completely replaced by "o", and only "o"*/
SELECT TOP (10) sys.objects.* FROM sys.objects o
Thus, when you write:
UPDATE base_table_name
SET ...
FROM base_table_name btn
INNER JOIN ... ON ... = btn.key_col
You're not UPDATEing the table that's been joined, you're updating a completely separate rendering of the table, i.e., almost as if you wrote the equivalent of:
UPDATE base_table_name
SET ...
FROM base_table_name
{?join?} base_table_name btn
INNER JOIN ... ON ... = btn.key_col
One of the huge advantages of aliases is that they allow the same table to be referenced twice in a query (such as translating both origin_state_code and dest_state_code from the same row). But you want to make sure, of course, that you're doing that deliberately and not by accident.
And I've seen really odd results from doing it. I'm not sure if SQL is doing a CROSS JOIN or some other type of "match", but it's very dangerous to find out. You'll get results you don't expect at all.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
April 10, 2018 at 12:51 pm
sgmunson - Monday, April 9, 2018 6:27 AMUnfortunately, you missed his point entirely. The structure you have only allows for the current job and most recent previous job to be kept track of. If you'll NEVER EVER track any further history than the very last job someone had, then perhaps it doesn't matter, but data structures of that nature have their own set of difficulties that the alternative solves in a much more performant and scalable way. Why NOT be able to track the entire history of jobs that a given person could ever have?
Sorry if I misunderstood. I probably didn't make myself very clear.
I do "NOT" want or need to be able to track the entire history of jobs. We have this already in our Payroll system. We prefer to not have multiple places for the same information, especially when one (this one) is a manual entry and it is too easy to make mistakes. This will be used for current From and To transfer of individuals that happen throughout the week; it is not my job tracking system. A simple system to track the current labor force and automate emails to the managers of the transfers, both from and to their projects/jobs.
I hope this helps a bit.
April 11, 2018 at 10:59 am
Ken at work - Tuesday, April 10, 2018 12:51 PMsgmunson - Monday, April 9, 2018 6:27 AMUnfortunately, you missed his point entirely. The structure you have only allows for the current job and most recent previous job to be kept track of. If you'll NEVER EVER track any further history than the very last job someone had, then perhaps it doesn't matter, but data structures of that nature have their own set of difficulties that the alternative solves in a much more performant and scalable way. Why NOT be able to track the entire history of jobs that a given person could ever have?Sorry if I misunderstood. I probably didn't make myself very clear.
I do "NOT" want or need to be able to track the entire history of jobs. We have this already in our Payroll system. We prefer to not have multiple places for the same information, especially when one (this one) is a manual entry and it is too easy to make mistakes. This will be used for current From and To transfer of individuals that happen throughout the week; it is not my job tracking system. A simple system to track the current labor force and automate emails to the managers of the transfers, both from and to their projects/jobs.
I hope this helps a bit.
Thanks for that update. It makes things much more clear. Just to cover all bases, don't be too surprised when someone notices the benefit of what you're doing and wants to have you start tracking the history even if it duplicates other data. Seen that before so many times that I've concluded that it may well be a foregone conclusion.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
April 12, 2018 at 5:42 am
ScottPletcher - Monday, April 9, 2018 3:39 PM
SET ANSI_NULLS ON;
SET QUOTED_IDENTIFIER ON;
GO
CREATE TRIGGER udLaborTracking__TRG_UPD_Set_LastJob
ON dbo.udLaborTracking
AFTER UPDATE
AS
SET NOCOUNT ON;
IF UPDATE(Job)
BEGIN
UPDATE ult /*this is critical: update the alias name, not the original table name*/
SET LastJob = d.Job
FROM dbo.udLaborTracking AS ult
INNER JOIN deleted AS d ON d.Co = ult.Co AND d.Employee = ult.Employee
WHERE ult.Job <> d.Job OR d.Job IS NULL
END /*IF*/
Thank you for your example of this. It seems so easy now. I tried it and it works well.
Again, thank you for your help.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply