sql server new users: Help with Trigger -- sql server new users

You're in the

sql server new users

group:

Help with Trigger

Nirmal Singh
3/29/2007 12:00:00 AM

sql server new users:

I have two tables called Managers and Employees.

The Managers table is (partially) structured as follows:
LineManager VarChar(10) Not Null
SummaryDate DateTime Null

This has a one-to-many relationship with the Employees table which is
(partially) structured as follows:
LineManager VarChar(10) Not Null
Employee VarChar(10) Not Null
Processed Bit Null

Each Manager has a varying number of employees, which they need to process.
I need to insert the current DateTime in the SummaryDate field when the last
employee for a particular manager has been processed.

I know I need to set up an Update Trigger on the Employees table, but how
do I check whether all employees for this manager have been processed?

Nirmal Singh

Re: Help with Trigger

Nirmal Singh
3/29/2007 12:00:00 AM

Sorry Tom, I meant when all Employees for a Manager have been processed.

Nirmal

[quoted text, click to view]

Re: Help with Trigger

Tom Moreau
3/29/2007 7:04:22 AM

How do you define "Last Employee"?

--
Tom

----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
..
[quoted text, click to view]
I have two tables called Managers and Employees.

The Managers table is (partially) structured as follows:
LineManager VarChar(10) Not Null
SummaryDate DateTime Null

This has a one-to-many relationship with the Employees table which is
(partially) structured as follows:
LineManager VarChar(10) Not Null
Employee VarChar(10) Not Null
Processed Bit Null

Each Manager has a varying number of employees, which they need to process.
I need to insert the current DateTime in the SummaryDate field when the last
employee for a particular manager has been processed.

I know I need to set up an Update Trigger on the Employees table, but how
do I check whether all employees for this manager have been processed?

Nirmal Singh

Re: Help with Trigger

Tom Moreau
3/29/2007 5:30:52 PM

Try:

create trigger tri_Employees on Employees after insert, update
as
if @@rowcount = 0
return

update Managers
set
SummaryDate = getdate ()
where exists
(
select
*
from
(
select distinct
Employee
from
inserted
) i
join
Employees e on e.Employee = i.Employee
where
e.Processed = 1
group by
e.Employee
having
count (*) = (select count (*) from Employee e2
where e.Employee = e2.Employee)
)

--
Tom

----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
..
[quoted text, click to view]
Sorry Tom, I meant when all Employees for a Manager have been processed.

Nirmal

[quoted text, click to view]

Re: Help with Trigger

Nirmal Singh
3/30/2007 12:00:00 AM

Thanks for that Tom.

Nirmal

[quoted text, click to view]

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages in the sql server new users group.

home · blog · groups

Questions? Comments? Contact the dn