Trigger to update and inser a count in column

Question

Post reply

Trigger to update and inser a count in column

pallavi.unde

SSCommitted

Points: 1987
More actions
December 12, 2011 at 5:36 am

#253283

Hello,
Here i have a problem in trigger execution,
there are 3 tables in which
2 tables are Master table and 1 is transition
Table 1 : Employeemaster
Empid empname
1 Rock
2 Leena
3 Meena
4 Nits
Table 2:- Projectmaster
projid projname
10 A
20 B
30 C
40 D
Transition
Projid empid
10 1
10 2
10 3
20 4
20 1
30 2
40 3
Now i want to maintain a employeeid count in project master table
for that i need to fire a trigger on it for update and insert mode
e.g please see the below example,
if any record added in projectmaster and still no employee assigned then it should show 0
and if any employee leaves the project then it should automatic update employee record
projid projname count
10 A 3
20 B 2
30 C 1
40 D 1
please reply
Thanks & Regards,
Pallavi

Viewing 15 posts - 1 through 15 (of 20 total)

You must be logged in to reply to this topic. Login to reply

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 1

John Mitchell-245523

SSC Guru

Points: 148809

December 12, 2011 at 5:43 am

#1419977

What have you tried so far?

John

pallavi.unde SSCommitted Points: 1987 More actions · Answer 2

still i have one query which generate the count but m thinking for trigger so that at project background it will save time of query execution.

is there any possibility to do so???

Thanks & Regards,
Pallavi

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 3

Yes, I think it's possible to do this with a trigger. But I'm not doing all your work for you. Have a try, and post back if you come up against any specific problems.

By the way, it's customary and courteous to supply your table DDL and sample data in the form of CREATE TABLE statements and INSERT statements respectively. That way, anyone trying to help you doesn't have to waste their time building their own test environment for you.

John

pallavi.unde SSCommitted Points: 1987 More actions · Answer 4

Is it possible to you to give some guideline or weblink for this????

Thanks & Regards,
Pallavi

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

Google?

If you want our help, post the table DDL and the current trigger code at the very least.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Devendrakumar SSC-Forever Points: 42493 More actions · Answer 6

if any record added in projectmaster and still no employee assigned then it should show 0
and if any employee leaves the project then it should automatic update employee record

If you are updating it in Stored Procedure (which you should do) then you don't need Triggers. Just add update code in your Stored Procedure.

Lowell SSC Guru Points: 323518 More actions · Answer 7

i'd recommend forgetting about putting the count in the table.

instead, create a view that is uesed to calculate the count based on a join; then the count is correct every time, and is not subject to the trigger in the database failing, or being skipped by a bcp/bulk insert, or being disabled by a developer.

the view will always be correct.

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!

Devendrakumar SSC-Forever Points: 42493 More actions · Answer 8

Lowell (12/12/2011)
i'd recommend forgetting about putting the count in the table.
instead, create a view that is uesed to calculate the count based on a join; then the count is correct every time, and is not subject to the trigger in the database failing, or being skipped by a bcp/bulk insert, or being disabled by a developer.
the view will always be correct.

It’s good idea subject to View Definition (simple Vs complex) & Data Volume.

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 9

Lowell (12/12/2011)
i'd recommend forgetting about putting the count in the table.
instead, create a view that is uesed to calculate the count based on a join; then the count is correct every time, and is not subject to the trigger in the database failing, or being skipped by a bcp/bulk insert, or being disabled by a developer.
the view will always be correct.

Lowell, I did think about that, but then I thought that if employees come and go, then the only way to know how may you had at any one time is either to change the design of the database, or to maintain a count somewhere.

John

Lowell SSC Guru Points: 323518 More actions · Answer 10

Dev and John i thought we might be overthinking the complexity of the original post, and wanted to throw the view possibility out there as well;

It seemed to me he wanted the count in the "project master" table simply for reporting purposes; i didn't see any significant business logic so far, but most forum posts try to simplyfy the issue for understandability anyway.

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!

tfifield SSCrazy Eights Points: 9655 More actions · Answer 11

I'm with Lowell on this one. The few times I've come across aggregates maintained by triggers, they were flaky and I replaced them with aggregate views, which performed well enough.

Todd Fifield

pallavi.unde SSCommitted Points: 1987 More actions · Answer 12

as per your guidance i m trying to create a view but it give error

script is

create view trialbase as

select distinct count (a.collegeid)as becount,d.name,a.StreamGroupId,b.DegreeId,b.DegreeName,c.CourseName

from college_degreestreamtransition a inner join college_degreemaster b on a.degreeid=b.degreeid inner join college_coursetypemaster c on c.coursetypeid=a.coursetypeid inner join

college_trial d on d.id=a.StreamGroupId

where d.popularstream='Y' and b.degreeid=41 group by d.name,a.StreamGroupId,b.DegreeId,b.DegreeName,c.CourseName order by becount desc

Error :-

Msg 1033, Level 15, State 1, Procedure trialbase, Line 5

The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP or FOR XML is also specified.

Thanks & Regards,
Pallavi

Devendrakumar SSC-Forever Points: 42493 More actions · Answer 13

pallavi.unde (12/13/2011)
as per your guidance i m trying to create a view but it give error
script is
create view trialbase as
select distinct count (a.collegeid)as becount,d.name,a.StreamGroupId,b.DegreeId,b.DegreeName,c.CourseName
from college_degreestreamtransition a inner join college_degreemaster b on a.degreeid=b.degreeid inner join college_coursetypemaster c on c.coursetypeid=a.coursetypeid inner join
college_trial d on d.id=a.StreamGroupId
where d.popularstream='Y' and b.degreeid=41 group by d.name,a.StreamGroupId,b.DegreeId,b.DegreeName,c.CourseName order by becount desc
Error :-
Msg 1033, Level 15, State 1, Procedure trialbase, Line 5
The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP or FOR XML is also specified.

On behalf of Lowell 😉

Did you read the error message carefully? Now check Lowell's signature. That's your answer.

pallavi.unde SSCommitted Points: 1987 More actions · Answer 14

yes i read it now applied top(100) percent but error shown that ,

Msg 156, Level 15, State 1, Procedure trialbase2, Line 2

Incorrect syntax near the keyword 'distinct'.

Thanks & Regards,
Pallavi