April 9, 2014 at 8:24 am
Hello again!
This is my first trigger attempt and not sure how to start. Basically, I have a table (dbo.RemLactation) that when there is a change or new addition to the table for the columns LacDryOffDate or the LacCalvingDate, I need to trigger a script that will search and fix the all 0's bug in the dbo.PrmMilkVisit table.
The idea is when a farmer enters a dry off date or a calving date for an animal then this script will run.
The script that should be triggered is:
UPdate dbo.PrmMilkVisit
Set MviLFXPosition = 60, MviLFYPosition = 660, MviLFZPosition = 510, MviLRXPosition = 30, MviLRYPosition = 780,
MviLRZPosition = 520, MviRFXPosition = -70, MviRFYPosition = 670, MviRFZPosition = 500, MviRRXPosition = -30,
MviRRYPosition = 790, MviRRZPosition = 520
Where MviLFXPosition = 0 AND MviLFYPosition = 0 and MviLFZPosition = 0 and MviLRXPosition = 0 and MviLRYPosition = 0
and MviLRZPosition = 0 and MviRFXPosition = 0 and MviRFYPosition = 0 and MviRFZPosition = 0 and MviRRXPosition = 0
and MviRRYPosition = 0 and MviRRZPosition = 0
See attachment.
Thank you very much!
April 9, 2014 at 8:35 am
The first thing you need to do is identify if this is an INSTEAD OF trigger or an AFTER trigger. I believe that you want an INSTEAD OF trigger so that you don't insert zeros. Your current script works as an after trigger since you don't check the values in the inserted table. To create the actual trigger follow BOL here
Your current script will only update rows where every column (listed) is equal to zero. Is that what you intend or do you want to update the value if it is zero? i.e. case when MviRRYposition = 0 then 790 etc.
As I stated above you have access to two "tables" called inserted and deleted which shows you the new values (inserted) and the old values (deleted). You can check these tables first before running your update statement so that it only runs when it is needed.
April 9, 2014 at 8:49 am
I simply want to monitor the dbo.RemLactation table on the 2 columns and when there is a change then run my script, the script searches the dbo.PrmMilkVisit table and when there is a case where all are equal to 0 then fix that, it is ok if 1 of them is equal to 0 or even a couple but not all of the coordinates. So case 0 is no good, I don't want to change a data set that was 0, 100, 346 on LF to 100, 100, 346 that would be bad, but if all (LF, RF, RR and LR) are 0,0,0 0,0,0 0,0,0 0,0,0 then these need to be changed. These events are random in the table and I think the simplest fix is to monitor the RemLactation table for changes and trigger fixing these 0 problems.
April 9, 2014 at 8:52 am
it seems to me that you might be able to replace the trigger with default values.
the WHERE criteria is huge in your example, and ALL must be zero to get the trigger to punch in changes?
As Kieth mentioned, he's guessing it's probably to fix any zero values, so it doesn't quite look right; i'd agree i'd suspect the intention is to replace zeros with expected values.
here's a trigger that actually does exactly what the update should do, and a second one that does what i agree might be the desired outcome.
CREATE TRIGGER TR_PrmMilkVisitExample1 ON dbo.PrmMilkVisit
FOR UPDATE
AS
UPDATE MyTarget
SET MyTarget.MviLFXPosition = 60,
MyTarget.MviLFYPosition = 660,
MyTarget.MviLFZPosition = 510,
MyTarget.MviLRXPosition = 30,
MyTarget.MviLRYPosition = 780,
MyTarget.MviLRZPosition = 520,
MyTarget.MviRFXPosition = -70,
MyTarget.MviRFYPosition = 670,
MyTarget.MviRFZPosition = 500,
MyTarget.MviRRXPosition = -30,
MyTarget.MviRRYPosition = 790,
MyTarget.MviRRZPosition = 520
FROM dbo.PrmMilkVisit MyTarget
INNER JOIN INSERTED T1
ON MyTarget.LacId = T1.LacId --only the records updated in this set
WHERE MyTarget.MviLFXPosition = 0
AND MyTarget.MviLFYPosition = 0
AND MyTarget.MviLFZPosition = 0
AND MyTarget.MviLRXPosition = 0
AND MyTarget.MviLRYPosition = 0
AND MyTarget.MviLRZPosition = 0
AND MyTarget.MviRFXPosition = 0
AND MyTarget.MviRFYPosition = 0
AND MyTarget.MviRFZPosition = 0
AND MyTarget.MviRRXPosition = 0
AND MyTarget.MviRRYPosition = 0
AND MyTarget.MviRRZPosition = 0
CREATE TRIGGER TR_PrmMilkVisitExample2 ON dbo.PrmMilkVisit
FOR UPDATE
AS
UPDATE MyTarget
SET MyTarget.MviLFXPosition = CASE WHEN T1.MviLFXPosition = 0 THEN 60 ELSE T1.MviLFXPosition END,
MyTarget.MviLFYPosition = CASE WHEN T1.MviLFYPosition = 0 THEN 660 ELSE T1.MviLFYPosition END,
MyTarget.MviLFZPosition = CASE WHEN T1.MviLFZPosition = 0 THEN 510 ELSE T1.MviLFZPosition END,
MyTarget.MviLRXPosition = CASE WHEN T1.MviLRXPosition = 0 THEN 30 ELSE T1.MviLRXPosition END,
MyTarget.MviLRYPosition = CASE WHEN T1.MviLRYPosition = 0 THEN 780 ELSE T1.MviLRYPosition END,
MyTarget.MviLRZPosition = CASE WHEN T1.MviLRZPosition = 0 THEN 520 ELSE T1.MviLRZPosition END,
MyTarget.MviRFXPosition = CASE WHEN T1.MviRFXPosition = 0 THEN -70 ELSE T1.MviRFXPosition END,
MyTarget.MviRFYPosition = CASE WHEN T1.MviRFYPosition = 0 THEN 670 ELSE T1.MviRFYPosition END,
MyTarget.MviRFZPosition = CASE WHEN T1.MviRFZPosition = 0 THEN 500 ELSE T1.MviRFZPosition END,
MyTarget.MviRRXPosition = CASE WHEN T1.MviRRXPosition = 0 THEN -30 ELSE T1.MviRRXPosition END,
MyTarget.MviRRYPosition = CASE WHEN T1.MviRRYPosition = 0 THEN 790 ELSE T1.MviRRYPosition END,
MyTarget.MviRRZPosition = CASE WHEN T1.MviRRZPosition = 0 THEN 52 ELSE T1.MviRRZPosition END
FROM dbo.PrmMilkVisit MyTarget
INNER JOIN INSERTED T1
ON MyTarget.LacId = T1.LacId --only the records updated in this set
Lowell
April 9, 2014 at 9:07 am
Lowell,
I get the following error:
Msg 207, Level 16, State 1, Procedure TR_PrmMilkVisitExample1, Line 20
Invalid column name 'LacId'.
Msg 207, Level 16, State 1, Procedure TR_PrmMilkVisitExample1, Line 20
Invalid column name 'LacId'.
When trying your first example.... These are 2 different tables not joined by any keys... PrmMilkVisit and RemLaction are different tables and I only want to run the script when there is a change to the RemLactation table for the columns dryoffdate or calvingdate.
April 9, 2014 at 9:17 am
domleg (4/9/2014)
Lowell,I get the following error:
Msg 207, Level 16, State 1, Procedure TR_PrmMilkVisitExample1, Line 20
Invalid column name 'LacId'.
Msg 207, Level 16, State 1, Procedure TR_PrmMilkVisitExample1, Line 20
Invalid column name 'LacId'.
When trying your first example.... These are 2 different tables not joined by any keys... PrmMilkVisit and RemLaction are different tables and I only want to run the script when there is a change to the RemLactation table for the columns dryoffdate or calvingdate.
The error referrs to the fact that LacID is not the real name of your PK, but since you didn't provide that Lowell had to guess.
As far as the logic for your trigger you want to look for all zero values in the PrmMilkVisit table when there is a change in the RemLaction table? But these tables have no way of joining to each other? If it isn't ever valid to have all zeros (for the columns mentioned) then why do you only want to run it when the two columns change in a different table that we can't join to?
April 9, 2014 at 9:33 am
Figured it out, thanks for pointing me in the right direction, I just right clicked the triggers in the remlactation table and clicked on new trigger, followed the step by step and replace all with what I wanted.
Final result was this... tested and working...
USE [Lely]
GO
/****** Object: Trigger [dbo].[A2FixTeatCoordinates] Script Date: 04/09/2014
11:27:58 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:<Author,,Name>
-- Create date: <Create Date,,>
-- Description:<Description,,>
-- =============================================
CREATE TRIGGER [dbo].[A2FixTeatCoordinates]
ON [dbo].[RemLactation]
AFTER INSERT,DELETE,UPDATE
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for trigger here
UPdate dbo.PrmMilkVisit
Set MviLFXPosition = 60, MviLFYPosition = 660, MviLFZPosition = 510, MviLRXPosition =
30, MviLRYPosition = 780,
MviLRZPosition = 520, MviRFXPosition = -70, MviRFYPosition = 670, MviRFZPosition = 500,
MviRRXPosition = -30,
MviRRYPosition = 790, MviRRZPosition = 520
Where MviLFXPosition = 0 AND MviLFYPosition = 0 and MviLFZPosition = 0 and
MviLRXPosition = 0 and MviLRYPosition = 0
and MviLRZPosition = 0 and MviRFXPosition = 0 and MviRFYPosition = 0 and MviRFZPosition
= 0 and MviRRXPosition = 0
and MviRRYPosition = 0 and MviRRZPosition = 0
END
GO
April 9, 2014 at 10:45 am
domleg (4/9/2014)
Figured it out, thanks for pointing me in the right direction, I just right clicked the triggers in the remlactation table and clicked on new trigger, followed the step by step and replace all with what I wanted.Final result was this... tested and working...
USE [Lely]
GO
/****** Object: Trigger [dbo].[A2FixTeatCoordinates] Script Date: 04/09/2014
11:27:58 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:<Author,,Name>
-- Create date: <Create Date,,>
-- Description:<Description,,>
-- =============================================
CREATE TRIGGER [dbo].[A2FixTeatCoordinates]
ON [dbo].[RemLactation]
AFTER INSERT,DELETE,UPDATE
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for trigger here
UPdate dbo.PrmMilkVisit
Set MviLFXPosition = 60, MviLFYPosition = 660, MviLFZPosition = 510, MviLRXPosition =
30, MviLRYPosition = 780,
MviLRZPosition = 520, MviRFXPosition = -70, MviRFYPosition = 670, MviRFZPosition = 500,
MviRRXPosition = -30,
MviRRYPosition = 790, MviRRZPosition = 520
Where MviLFXPosition = 0 AND MviLFYPosition = 0 and MviLFZPosition = 0 and
MviLRXPosition = 0 and MviLRYPosition = 0
and MviLRZPosition = 0 and MviRFXPosition = 0 and MviRFYPosition = 0 and MviRFZPosition
= 0 and MviRRXPosition = 0
and MviRRYPosition = 0 and MviRRZPosition = 0
END
GO
This doesn't look right to me. You are not referencing inserted or deleted. That means this will run execute this code and update any values that meet your condition in the entire table, not just any rows inserted or updated. Why in the world do you have this running for a delete???
Also, do you realize that this will only modify data when every single condition is true? If ANY column in your where is something other than 0 this will not do anything. It will not do anything if ANY of these are NULL.
Where MviLFXPosition = 0 AND MviLFYPosition = 0 and MviLFZPosition = 0 and
MviLRXPosition = 0 and MviLRYPosition = 0
and MviLRZPosition = 0 and MviRFXPosition = 0 and MviRFYPosition = 0 and MviRFZPosition
= 0 and MviRRXPosition = 0
and MviRRYPosition = 0 and MviRRZPosition = 0
_______________________________________________________________
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/
April 9, 2014 at 10:56 am
Believe me it works, I tested it, I had a database with 121 entries in the PrmMilkVisit table of all 0's, I simply entered a dry off date for 1 animal and presto, there were no more entries in the PrmMilkVisit table with all 0's.
This is a quick an dirty fix for something the real programmers wont fix for us coming from the robot's embedded software database.... for some odd reason random events get created in the PrmMilkVisit table where all the coordinates are 0 when they should be numbers (X,Y,Z robotic coordinates).
My fix is simply replace these 0's with a calculated average for each column..... but since these are random and the issue lies when the farmer enters a dry off date and the last milking for this cow just happens to be all 0's then the next time he enters the calving date, the robot doesn't store the coordinates properly for up to 7 milkings....when it should right away to automate the second visit to the robot, instead he has to position the arm up to 7 different visits to get them automated...very frustrating.
I simulated manually entering values on a problem cow and the second time in the robot she milked immediately without asking for manually setting the arm again.
So I came up with this idea that these 0 anomalies can be fixed every time the farmer is entering in new cows or calving dates and dry off dates...
It works like a charm....
April 9, 2014 at 11:06 am
I believe that it works, but it is not very efficient. It that is fine with you then problem solved. Otherwise you would want to use something like Lowell suggested and maybe add insert :FOR UPDATE, INSERT With his trigger you are adding the trigger to the correct table and it won't have to scan the entire table just the ones that were updated or inserted. The only change you have to make with his code is to use the correct PK (not lacID).
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply