Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

1st Trigger Attempt Expand / Collapse
Author
Message
Posted Wednesday, April 9, 2014 8:24 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, May 8, 2014 1:49 PM
Points: 7, Visits: 14
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!





  Post Attachments 
1stTriggerAttempt.JPG (9 views, 169.28 KB)
Post #1559979
Posted Wednesday, April 9, 2014 8:35 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Tuesday, July 1, 2014 9:14 PM
Points: 564, Visits: 851
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.




Microsoft Certified Master - SQL Server 2008
Follow me on twitter: @keith_tate

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1559988
Posted Wednesday, April 9, 2014 8:49 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, May 8, 2014 1:49 PM
Points: 7, Visits: 14
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.

Post #1560000
Posted Wednesday, April 9, 2014 8:52 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 11:09 AM
Points: 12,881, Visits: 31,819
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1560002
Posted Wednesday, April 9, 2014 9:07 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, May 8, 2014 1:49 PM
Points: 7, Visits: 14
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.

Post #1560010
Posted Wednesday, April 9, 2014 9:17 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Tuesday, July 1, 2014 9:14 PM
Points: 564, Visits: 851
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?




Microsoft Certified Master - SQL Server 2008
Follow me on twitter: @keith_tate

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1560020
Posted Wednesday, April 9, 2014 9:33 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, May 8, 2014 1:49 PM
Points: 7, Visits: 14
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


Post #1560029
Posted Wednesday, April 9, 2014 10:45 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 3:21 PM
Points: 13,083, Visits: 11,918
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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1560077
Posted Wednesday, April 9, 2014 10:56 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, May 8, 2014 1:49 PM
Points: 7, Visits: 14
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....
Post #1560093
Posted Wednesday, April 9, 2014 11:05 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Tuesday, July 1, 2014 9:14 PM
Points: 564, Visits: 851
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).




Microsoft Certified Master - SQL Server 2008
Follow me on twitter: @keith_tate

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1560103
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse