Trigger to Update with MAX Value

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Trigger to Update with MAX Value

Author

Topic

mimuk
Starting Member

19 Posts

Posted - 2011-06-07 : 06:32:39

I hope someone can help with a trigger I am trying to create.
I need to accomplish the following...

Here is my table structure

Opportunity
oppo_opportunityid, oppo_forecast, oppo_certainty,
1, NULL, NULL,
2, NULL, NULL,

Project
proj_projectid, proj_opportunityid, proj_forecast, proj_certainty,
101, 1, 1000, 50,
102, 1, 500, 75,
103, 2, 750, 25,

I need a trigger on Project that it updates Opportunity (oppo_forecast value)
with the MAX proj_forecast values for the all related proj_opportunityid's
Then updates the the oppo_certainty to be the proj_certainty associated with that highest forecast value

So I should get

oppo_opportunityid, oppo_forecast oppo_certainty
1, 1000, 50,
2, 750, 25,

Many thanks,

Mim

mimuk
Starting Member

19 Posts

Posted - 2011-06-07 : 09:49:08

even if someone could help with getting the select declaration right, that would help.... i.e
Update Opportunity set Oppo_certainy = proj_certainty WHERE (Select proj_projectid, proj_certainty, proj_forecast from Project where proj_forecast = (SELECT MAX(proj_forecast) from Project)

etc etc.

Cheers,

Mim

Ifor
Aged Yak Warrior

700 Posts

Posted - 2011-06-07 : 10:48:04

With SQL2005 and above an update can be done with something like:


;WITH ProjectOrder
AS
(
	SELECT proj_projectid, proj_opportunityid, proj_forecast, proj_certainty
		ROW_NUMBER() OVER (PARTITION BY proj_opportunityid ORDER BY proj_forecast DESC) AS RowNum
	FROM Project
)
UPDATE O
SET oppo_forecast = P.proj_forecast
	,oppo_certainty = P.proj_certainty
FROM Opportunity O
	JOIN ProjectOrder P
		ON O.oppo_opportunityid = P.proj_opportunityid
			AND P.RowNum = 1

Your post is unclear on:
1. why you would want to use a trigger.
2. what you would want a trigger to do.
(Insert into Opportunity, only update, insert and update etc)

You may also get better responses if you specify the version of SQL you are using.

mimuk
Starting Member

19 Posts

Posted - 2011-06-07 : 11:04:44

Ideally I would like to do it in the application tablescript...
The following updates the corresponding Forecast perfectly, However, I am struggling to update the certainty from the same row

var pvRecord = eWare.FindRecord('ProjectValue', WhereClause);
var projRecord = eWare.FindRecord('Project','proj_projectID='+pvRecord.prva_projectid);
var OppoRecord = eWare.FindRecord('Opportunity','oppo_opportunityid ='+projRecord.proj_opportunityid)

{

var OppoID = projRecord("proj_opportunityid");

var sql = "Update Opportunity set Oppo_forecast = (Select MAX(proj_forecast) from Project " +

" where proj_deleted is null and proj_OpportunityID =" + OppoID +

" ) where oppo_OpportunityID = " + OppoID;

Updatequery = eWare.CreateQueryObj(sql);

Updatequery.ExecSql();

}

jcelko
Esteemed SQL Purist

547 Posts

Posted - 2011-06-07 : 22:04:38

People cannot read your mind, so post your code and clear specs if you really want help. Please post real DDL and not narrative or your own personal programming language. Learn to use ISO-11179 rules for the data element names, avoid needless dialect and use ISO-8601 temporal formats, codes and so forth. Please tell us what SQL product and release you are using. Tell us if you can change the DDL or if you are stuck with it.

>> I hope someone can help with a trigger I am trying to create. <<

Trigers are procedural code, so goo programmers do not want to write them at all!

>> Here is my table structure <<

This is a mess; let's try to fix it Table names are plural or collective nouns. Why did you prefix columns with a table name?

CREATE TABLE Opportunities
(opportunity_id INTEGER NOT NULL PRIMARY KEY,
forecast_score INTEGER,
certainty_score INTEGER);

This is an awful design. This table is both useless and dangerous. Throw it out.

CREATE TABLE Projects
(projects_id INTEGER NOT NULL PRIMARY KEY,
opportunity_id INTEGER NOT NULL
REFERENCES Opportunities (opportunity_id)
forecast_score INTEGER NOT NULL,
certainty_score INTEGER NOT NULL);

INSERT INTO Projects
VALUES (101, 1, 1000, 50),
(102, 1, 500, 75),
(103, 2, 750, 25);

>> I need a trigger on Projects that it updates Opportunities. forecast_score value) with the MAX proj_forecast_score values for the all related Projects.opportunity_id's. Then updates the the oppo_certainty_score to be the proj_certainty_score associated with that highest forecast_score value <<

NO!! Put this in a VIEW.

CREATE VIEW BestProjects (opportunity_id, forecast_score, certainty_score)
AS
SELECT DISTINCT P1.opportunity_id, P1.forecast_score, P1.certainty_score
FROM Projects AS P1
WHERE P1.forecast_score
= (SELECT MAX(P2.forecast_score)
FROM Projects AS P2
WHERE P1.opportunity_id = P2.opportunity_id);

Your approach to SQL is completely wrong. You want to mimic the way we might have programmed with punch cards when we had to have physical files.

--CELKO--
Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources