help me code for trigger:

Question

help me code for trigger:

letanduc

Grasshopper

Points: 13
More actions
December 6, 2010 at 2:33 am

#141575

2 Posts
Posted - 12/06/2010 : 03:42:33 Show Profile Email Poster Edit Topic Reply with Quote
Department_ID nvarchar(2)
Possition_ID nvarchar(2)
give me code to solve : with each Department_ID
have only <=1 Department manager (Possition_ID='00')
thanks

Viewing 7 posts - 1 through 6 (of 6 total)

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

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

Please post table definitions, sample data and expected results as per http://www.sqlservercentral.com/articles/Best+Practices/61537/

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

Steve Jones - SSC Editor SSC Guru Points: 736455 More actions · Answer 2

Please also try to solve this yourself. We are more than willing to help, but we are not here to do your work, or homework.

WayneS SSC Guru Points: 95460 More actions · Answer 3

First, can you verify that this is NOT a homework / test / interview question?

Instead of doing this through a trigger, have you tried a filtered indexed view with either a unique index or primary key on the column?

Something like this for the view:

SELECT (list of columns from base table)

FROM (whatever the base table is)

WHERE Possition_ID = '00';

Note that to create an index on a view, you first need to materialize it by adding a clustered index. The following might be a good candidate for that, but if not you will need another clustered index.

Then create a unique or PK constraint on Department_ID + Possition_ID.

In SQL 2008, you could just create a unique, filtered index on Department_ID + Possition_ID.

This is usually more efficient than trying to maintain through a trigger.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

letanduc Grasshopper Points: 13 More actions · Answer 4

I want: on my table (Department_ID nvarchar(2),Possition_ID nvarchar(2))

never occur: exists

SELECT Department_ID FROM MyTable

WHERE Possition_ID = '00'

GROUP BY Department_ID HAVING COUNT(Possition_ID) >= 2

Help me

Steve Jones - SSC Editor SSC Guru Points: 736455 More actions · Answer 5

This

letanduc (12/6/2010)
I want: on my table (Department_ID nvarchar(2),Possition_ID nvarchar(2))
never occur: exists

Makes no sense. What is "never occur: exists?"

Please spend more than 10 sec and write something that describes the behavior you are looking for.

Carlton Leach SSCarpal Tunnel Points: 4202 More actions · Answer 6

As stated by the others: happy to help not do your job for you/get you a job based on answering SQL questions for you.

Please clearly state what you are trying to achieve.