SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


1st Trigger Attempt


1st Trigger Attempt

Author
Message
domleg
domleg
Grasshopper
Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)

Group: General Forum Members
Points: 11 Visits: 22
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!
Attachments
1stTriggerAttempt.JPG (14 views, 169.00 KB)
Keith Tate
Keith Tate
SSCrazy
SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)

Group: General Forum Members
Points: 2388 Visits: 979
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
domleg
domleg
Grasshopper
Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)

Group: General Forum Members
Points: 11 Visits: 22
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.
Lowell
Lowell
SSC Guru
SSC Guru (74K reputation)SSC Guru (74K reputation)SSC Guru (74K reputation)SSC Guru (74K reputation)SSC Guru (74K reputation)SSC Guru (74K reputation)SSC Guru (74K reputation)SSC Guru (74K reputation)

Group: General Forum Members
Points: 74887 Visits: 40985
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
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!
domleg
domleg
Grasshopper
Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)

Group: General Forum Members
Points: 11 Visits: 22
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.
Keith Tate
Keith Tate
SSCrazy
SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)

Group: General Forum Members
Points: 2388 Visits: 979
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
domleg
domleg
Grasshopper
Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)

Group: General Forum Members
Points: 11 Visits: 22
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
Sean Lange
Sean Lange
SSC Guru
SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)

Group: General Forum Members
Points: 64786 Visits: 17979
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.

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)
domleg
domleg
Grasshopper
Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)

Group: General Forum Members
Points: 11 Visits: 22
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....
Keith Tate
Keith Tate
SSCrazy
SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)

Group: General Forum Members
Points: 2388 Visits: 979
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search