Post reply

Trigger to update and inser a count in column

  • 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

  • December 12, 2011 at 5:43 am

    #1419977

    What have you tried so far?

    John

  • December 12, 2011 at 5:51 am

    #1419984

    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

  • December 12, 2011 at 5:56 am

    #1419991

    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

  • December 12, 2011 at 6:13 am

    #1420013

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

    Thanks & Regards,
    Pallavi

  • December 12, 2011 at 6:17 am

    #1420017

    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

  • December 12, 2011 at 6:19 am

    #1420020

    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.

  • December 12, 2011 at 6:33 am

    #1420029

    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!

  • December 12, 2011 at 6:36 am

    #1420035

    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.

  • December 12, 2011 at 6:44 am

    #1420044

    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

  • December 12, 2011 at 6:49 am

    #1420049

    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!

  • December 12, 2011 at 2:46 pm

    #1420285

    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

  • December 13, 2011 at 12:13 am

    #1420375

    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

  • December 13, 2011 at 12:36 am

    #1420384

    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.

  • December 13, 2011 at 12:42 am

    #1420388

    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

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

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