June 29, 2018 at 6:08 am
Hello,
This is my first time trying to write a trigger.
I have two tables, a Jobs table and a Jobs_aggregate table.
When someone performs an insert on the jobs table I'm trying to write a trigger that will check to see if that job is already in the aggregate table and, if it is, update it with the new details from the "inserted" table and if not, create it with the details from the "inserted" table.
I'm struggling with the syntax and how to refer to the inserted table. It looks like I need to join it again but I'm not sure how?
Here is the code I have so far but it doesn't like me using inserted.customerid...........etc.
CREATETRIGGER [dbo].[Update_jobsAggregate]
ON [dbo].[Jobs]
FOR INSERT
AS
declare @foundguid as varchar(32)
Begin
set @foundguid = (select JA.Guid from jobs_aggregate JA --find out if the record exists in the aggregate table.
join inserted I
on
JA.customerid = I.customerid
and JA.vehicleid = I.vehicleid
and JA.serviceid = I.serviceid)
if @foundguid = '' --job is not in the aggregate table yet so insert it.
insert into jobs_aggregate (customerid,vehicleid,serviceid)
values (inserted.customerid,inserted.vehicleid,inserted.serviceid)
else -- job is already in the aggregate table so update it
update Jobs_Aggregate
set
jobs_Aggregate.customerid = inserted.customerid,
jobs_aggregate.vehicleid = inserted.vehicleid,
jobs_aggregate.serviceid = inserted.serviceid
end
These are just some of the fields involved, can anyone push me in the right direction with the correct syntax I need to refer to the "inserted" table in this way please?
Thank you in advance.
Paul.
June 29, 2018 at 7:10 am
I think I have made some progress but it's still not working.
This is the code I have now.....
ALTER TRIGGER [dbo].[Update_jobsAggregate]
ON [dbo].[Jobs]
FOR INSERT
AS
declare @foundguid as varchar(32)
Begin
set @foundguid = (select JA.Guid from jobs_aggregate JA --find out if the record exists in the aggregate table.
where ja.CustomerID = (select customerid from inserted) and
ja.vehicleid = (select vehicleid from inserted) and
ja.serviceid = (select serviceid from inserted))
if @foundguid = '' --job is not in the aggregate table yet so insert it.
insert into jobs_aggregate (customerid,vehicleid,serviceid)
select inserted.customerid,inserted.vehicleid,inserted.serviceid
from inserted
else -- job is already in the aggregate table so update it
update Jobs_Aggregate
set
jobs_Aggregate.customerid = (select inserted.customerid from inserted),
jobs_aggregate.vehicleid = (select inserted.vehicleid from inserted),
jobs_aggregate.serviceid = (select inserted.serviceid from inserted)
end
June 29, 2018 at 7:17 am
The inserted table is just that: a table. For most purposes, it behaves exactly like any other table.
The bigger issue here is that you have designed your trigger to only handle single-row inserts. If multiple rows are inserted, it will only process a single row and DISCARD THE REST.
CREATETRIGGER [dbo].[Update_jobsAggregate]
ON [dbo].[Jobs]
FOR INSERT
AS
declare @foundguid as varchar(32)
Begin
-- Do the update first. No sense in inserting records
-- and then immediately updating them. update JA
set
-- You don't need to update the fields that you're matching on.
-- They're already equal or they wouldn't have matched.
jobs_Aggregate.customerid = inserted.customerid,
jobs_aggregate.vehicleid = inserted.vehicleid,
jobs_aggregate.serviceid = inserted.serviceid
FROM Jobs_Aggregate JA
INNER JOIN Inserted I
ON JA.customerid = I.customerid
and JA.vehicleid = I.vehicleid
and JA.serviceid = I.serviceid)
insert into jobs_aggregate (customerid,vehicleid,serviceid)
SELECT I.customerid, I.vehicleid, I.serviceid
FROM jobs_aggregate JA
join inserted I
on JA.customerid = I.customerid
and JA.vehicleid = I.vehicleid
and JA.serviceid = I.serviceid)
WHERE JA.customerid IS NULL --find out if the record exists in the aggregate table.
end
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
June 29, 2018 at 7:17 am
Your trigger has a MAJOR flaw. It assumes there will only ever be a single row in inserted. In sql server triggers fire once per operation, not once per row. Your triggers need to be set based. You also should be using joins instead of subqueries everytime. And your update at the end is going to update the entire table because there is no where clause. I will see if I can cobble something together that might be close to what you need.
--edit--
Seems that Drew was posting at the same time I was. 🙂
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 29, 2018 at 7:18 am
CREATETRIGGER should be two words, CREATE TRIGGER.
If you ever insert more than one row into Jobs, the trigger will fail at SET @foundguid = (subquery), as the subquery will return more than one row.
The test for existing row ( if @foundguid = '' ) will never work on SQL Server, I believe this is an Oracle idiosyncrasy (or feature if you're so inclined). If no row is found then @foundguid will be NULL and the condition will be false, leading to an attempted UPDATE of the non-existent row. The condition will be true only if a row is found where the value of JA.Guid is an empty string, but in this case INSERT is the wrong action. Checking for a NULL value ( if @foundguid IS NULL) would be an improvement, but a better choice would be to replace the SET and IF statements with one IF EXISTS() command. But this will still be wrong (although not throw an error) for multi-row INSERTs.
There is no WHERE clause on your UPDATE statement, you will modify every row of the table. Unless there is a unique constraint on those three columns, in which case it will cause an error.
You could eventually figure out how to write the correct INSERT and UPDATE logic for this, but a MERGE statement would be easier.
June 29, 2018 at 7:34 am
Sean Lange - Friday, June 29, 2018 7:17 AMYour trigger has a MAJOR flaw. It assumes there will only ever be a single row in inserted. In sql server triggers fire once per operation, not once per row. Your triggers need to be set based. You also should be using joins instead of subqueries everytime. And your update at the end is going to update the entire table because there is no where clause. I will see if I can cobble something together that might be close to what you need.--edit--
Seems that Drew was posting at the same time I was. 🙂
Thanks Sean. I thought there would only ever be 1 record in inserted and that the trigger would fire as soon as a record is inserted and after that inserted is cleared
June 29, 2018 at 7:40 am
drew.allen - Friday, June 29, 2018 7:17 AMThe inserted table is just that: a table. For most purposes, it behaves exactly like any other table.
The bigger issue here is that you have designed your trigger to only handle single-row inserts. If multiple rows are inserted, it will only process a single row and DISCARD THE REST.
CREATETRIGGER [dbo].[Update_jobsAggregate]
ON [dbo].[Jobs]
FOR INSERT
AS
declare @foundguid as varchar(32)
Begin
-- Do the update first. No sense in inserting records
-- and then immediately updating them. update JA
set
-- You don't need to update the fields that you're matching on.
-- They're already equal or they wouldn't have matched.
jobs_Aggregate.customerid = inserted.customerid,
jobs_aggregate.vehicleid = inserted.vehicleid,
jobs_aggregate.serviceid = inserted.serviceid
FROM Jobs_Aggregate JA
INNER JOIN Inserted I
ON JA.customerid = I.customerid
and JA.vehicleid = I.vehicleid
and JA.serviceid = I.serviceid)insert into jobs_aggregate (customerid,vehicleid,serviceid)
SELECT I.customerid, I.vehicleid, I.serviceid
FROM jobs_aggregate JA
join inserted I
on JA.customerid = I.customerid
and JA.vehicleid = I.vehicleid
and JA.serviceid = I.serviceid)
WHERE JA.customerid IS NULL --find out if the record exists in the aggregate table.
end
Hi Drew,
I have altered the trigger but when I insert a record into the jobs table I am still not getting anything inserted into the Job_aggregates table.
Thank you
Paul
June 29, 2018 at 7:50 am
paul 69259 - Friday, June 29, 2018 7:34 AMSean Lange - Friday, June 29, 2018 7:17 AMYour trigger has a MAJOR flaw. It assumes there will only ever be a single row in inserted. In sql server triggers fire once per operation, not once per row. Your triggers need to be set based. You also should be using joins instead of subqueries everytime. And your update at the end is going to update the entire table because there is no where clause. I will see if I can cobble something together that might be close to what you need.--edit--
Seems that Drew was posting at the same time I was. 🙂Thanks Sean. I thought there would only ever be 1 record in inserted and that the trigger would fire as soon as a record is inserted and after that inserted is cleared
Yeah it is a very common misunderstanding. There is a row in inserted for every row being inserted. The same is true with deleted which is populated in both update and delete triggers.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 29, 2018 at 7:58 am
Scott Coleman - Friday, June 29, 2018 7:18 AMCREATETRIGGER should be two words, CREATE TRIGGER.If you ever insert more than one row into Jobs, the trigger will fail at SET @foundguid = (subquery), as the subquery will return more than one row.
The test for existing row ( if @foundguid = '' ) will never work on SQL Server, I believe this is an Oracle idiosyncrasy (or feature if you're so inclined). If no row is found then @foundguid will be NULL and the condition will be false, leading to an attempted UPDATE of the non-existent row. The condition will be true only if a row is found where the value of JA.Guid is an empty string, but in this case INSERT is the wrong action. Checking for a NULL value ( if @foundguid IS NULL) would be an improvement, but a better choice would be to replace the SET and IF statements with one IF EXISTS() command. But this will still be wrong (although not throw an error) for multi-row INSERTs.
There is no WHERE clause on your UPDATE statement, you will modify every row of the table. Unless there is a unique constraint on those three columns, in which case it will cause an error.
You could eventually figure out how to write the correct INSERT and UPDATE logic for this, but a MERGE statement would be easier.
Thank you Scott. I have altered the code to have if exists and it appears to work but I have no idea how to make it cope with multiple rows.
ALTER TRIGGER [dbo].[Update_jobsAggregate]
ON [dbo].[Jobs]
FOR INSERT
AS
declare @foundguid as varchar(32)
Begin
if exists (select JA.Guid from jobs_aggregate JA --find out if the record exists in the aggregate table.
where ja.CustomerID = (select customerid from inserted) and
ja.vehicleid = (select vehicleid from inserted) and
ja.serviceid = (select serviceid from inserted))
update Jobs_Aggregate
set
jobs_Aggregate.customerid = (select inserted.customerid from inserted),
jobs_aggregate.vehicleid = (select inserted.vehicleid from inserted),
jobs_aggregate.serviceid = (select inserted.serviceid from inserted)
else
insert into jobs_aggregate (customerid,vehicleid,serviceid)
select inserted.customerid,inserted.vehicleid,inserted.serviceid
from inserted
end
June 29, 2018 at 8:09 am
You have to get the scalar value logic out of your head to start thinking about this as a set. You could use MERGE but separate insert and update statements is just as effective (and a lot simpler to debug than a merge). Also, the subqueries can be eliminated by using joins. Your trigger would something pretty close to this. I don't think your update though is really what you want because it will join only when the values you are updating are the same which renders the update kind of pointless. I am not really sure what you are trying to do for the update portion of this.
ALTER TRIGGER [dbo].[Update_jobsAggregate]
ON [dbo].[Jobs]
FOR INSERT
AS
update ja
set customerid = i.customerid,
vehicleid = i.vehicleid,
serviceid = i.serviceid
from jobs_aggregate ja
join inserted i on ja.customerid = i.customerid
AND ja.vehicleid = i.vehicleid
AND ja.serviceid = i.serviceid
insert into jobs_aggregate
(
customerid,
vehicleid,
serviceid
)
select i.customerid,
i.vehicleid,
i.serviceid
from inserted i
left join jobs_aggregate ja on ja.customerid = i.customerid
AND ja.vehicleid = i.vehicleid
AND ja.serviceid = i.serviceid
where ja.customerid IS NULL
end
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 29, 2018 at 8:12 am
This part of the query makes no sense and should be removed. If the match conditions are true, there is nothing to update.
update ja
set customerid = i.customerid,
vehicleid = i.vehicleid,
serviceid = i.serviceid
from jobs_aggregate ja
join inserted i on ja.customerid = i.customerid
AND ja.vehicleid = i.vehicleid
AND ja.serviceid = i.serviceid
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
June 29, 2018 at 8:16 am
paul 69259 - Friday, June 29, 2018 7:40 AMdrew.allen - Friday, June 29, 2018 7:17 AMThe inserted table is just that: a table. For most purposes, it behaves exactly like any other table.
The bigger issue here is that you have designed your trigger to only handle single-row inserts. If multiple rows are inserted, it will only process a single row and DISCARD THE REST.
CREATETRIGGER [dbo].[Update_jobsAggregate]
ON [dbo].[Jobs]
FOR INSERT
AS
declare @foundguid as varchar(32)
Begin
-- Do the update first. No sense in inserting records
-- and then immediately updating them. update JA
set
-- You don't need to update the fields that you're matching on.
-- They're already equal or they wouldn't have matched.
jobs_Aggregate.customerid = inserted.customerid,
jobs_aggregate.vehicleid = inserted.vehicleid,
jobs_aggregate.serviceid = inserted.serviceid
FROM Jobs_Aggregate JA
INNER JOIN Inserted I
ON JA.customerid = I.customerid
and JA.vehicleid = I.vehicleid
and JA.serviceid = I.serviceid)insert into jobs_aggregate (customerid,vehicleid,serviceid)
SELECT I.customerid, I.vehicleid, I.serviceid
FROM jobs_aggregate JA
join inserted I
on JA.customerid = I.customerid
and JA.vehicleid = I.vehicleid
and JA.serviceid = I.serviceid)
WHERE JA.customerid IS NULL --find out if the record exists in the aggregate table.
endHi Drew,
I have altered the trigger but when I insert a record into the jobs table I am still not getting anything inserted into the Job_aggregates table.
Thank you
Paul
Change the FROM clause to
FROM inserted I
LEFT OUTER JOIN jobs_aggregate JA
That is switch the order of the tables and make it a LEFT OUTER JOIN. (You could also make it a RIGHT OUTER JOIN without switching the tables, but that tends to be confusing for people whose native language reads from left to right.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
June 29, 2018 at 8:22 am
Sean Lange - Friday, June 29, 2018 8:09 AMYou have to get the scalar value logic out of your head to start thinking about this as a set. You could use MERGE but separate insert and update statements is just as effective (and a lot simpler to debug than a merge). Also, the subqueries can be eliminated by using joins. Your trigger would something pretty close to this. I don't think your update though is really what you want because it will join only when the values you are updating are the same which renders the update kind of pointless. I am not really sure what you are trying to do for the update portion of this.
ALTER TRIGGER [dbo].[Update_jobsAggregate]
ON [dbo].[Jobs]
FOR INSERT
AS
update ja
set customerid = i.customerid,
vehicleid = i.vehicleid,
serviceid = i.serviceid
from jobs_aggregate ja
join inserted i on ja.customerid = i.customerid
AND ja.vehicleid = i.vehicleid
AND ja.serviceid = i.serviceidinsert into jobs_aggregate
(
customerid,
vehicleid,
serviceid
)
select i.customerid,
i.vehicleid,
i.serviceid
from inserted i
left join jobs_aggregate ja on ja.customerid = i.customerid
AND ja.vehicleid = i.vehicleid
AND ja.serviceid = i.serviceid
where ja.customerid IS NULL
end
Thank you Sean,
I think I know what you are saying. If it's an insert then the job will not already exist so there's no need to check if it's already in the job aggregate table.
I think I was getting muddled up. I have to do an insert trigger and an update trigger. I can combine both into one trigger thinking about it can't i?
Thank you
Paul.
June 29, 2018 at 8:28 am
Phil Parkin - Friday, June 29, 2018 8:12 AMThis part of the query makes no sense and should be removed. If the match conditions are true, there is nothing to update.update ja
set customerid = i.customerid,
vehicleid = i.vehicleid,
serviceid = i.serviceid
from jobs_aggregate ja
join inserted i on ja.customerid = i.customerid
AND ja.vehicleid = i.vehicleid
AND ja.serviceid = i.serviceid
Agreed. I said as much in my post. 😀
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 29, 2018 at 8:29 am
paul 69259 - Friday, June 29, 2018 8:22 AMThank you Sean,I think I know what you are saying. If it's an insert then the job will not already exist so there's no need to check if it's already in the job aggregate table.
I think I was getting muddled up. I have to do an insert trigger and an update trigger. I can combine both into one trigger thinking about it can't i?
Thank you
Paul.
You can but that doesn't mean you should. I would suggest keeping them separate. It makes for code that is a LOT easier to debug and since the logic is completely different for an update and insert why smash them together?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 15 posts - 1 through 15 (of 29 total)
You must be logged in to reply to this topic. Login to reply