May 24, 2010 at 11:43 am
Hi
I am an amateur starting to learn in SQL. Your gently consultation would be my profound thanks!
I have created an one-row, multi-column table the values of which gets automatically updated by an external OPC link software. Let's say that Column1 = 12.345, Column2 = 123.456, the target result are in Column3 and 4. My objective is :
Whenever the Column1 value (12.345) is updated from external, the value of Column3 and 4 is the result of three formulas :
Formula1 : "variableA = Column2 - Column1". Example "A = 123.456 - 12.345". So A = 111.111.
Formula2 : "Column3 = digits before decimal point". So Column3 = 111.
Formula3 : "Column4 = digits after decimal point". So Column4 = 111.
I know that the trigger function can help me on this case. When I right click on the menu tree "triggers" attached to this table and select "New trigger", there will be a predefined "...SQLQuery1.sql" tab. But I don't know how to write its script and how to execute or make it help to my table.
Thank you in advance.
May 24, 2010 at 12:49 pm
Since you do not know how to write triggers, I am posting a sample of trigger.
USE Northwind
GO
DROP TRIGGER tr1
GO
CREATE TRIGGER tr1 ON Customers
FOR UPDATE AS
IF ( (SUBSTRING(COLUMNS_UPDATED(),1,1)=power(2,(3-1))
+ power(2,(5-1)))
AND (SUBSTRING(COLUMNS_UPDATED(),2,1)=power(2,(1-1)))
)
PRINT 'Columns 3, 5 and 9 updated'
GO
UPDATE Customers
SET ContactName=ContactName,
Address=Address,
Country=Country
GO
This is a simple sample of an Update trigger. (That is what you need in your case) Try to modify this and see if you can make it work.
But since you do not know much about trigger, I would prefer if you read about it on your Sql Help (Books online)
-Roy
May 24, 2010 at 1:28 pm
Doesn't really sound like a job for a trigger. A couple of computed columns should work very well here. Check Books Online for details of computed columns. Maybe start here - http://msdn.microsoft.com/en-us/library/ms186241%28v=SQL.90%29.aspx
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply