Create Trigger AgentLogin_Usename on agentlogin
for insert, update
as
IF Exists(
	select	hr.username
	from	hr
	join	inserted i
	on	i.username = hr.username
)
BEGIN
	RaisError('username exists in hr table', 16, 1)
	rollback
END

quote:

---

Originally posted by russell

I'd probably do this with a trigger on each table.

---

drop table t1
drop table t2
drop function dbo.username_count
go

create table dbo.t1 (username varchar(9))
create table dbo.t2 (username varchar(9))
go

create function dbo.username_count(@username varchar(9)) returns tinyint as
begin
return (select count(*) from (select * from t1 union all select * from t2) a where username = @username)
end
go

alter table dbo.t1 with nocheck add constraint t1_unique_username check (dbo.username_count(username) <= 1)
alter table dbo.t2 with nocheck add constraint t2_unique_username check (dbo.username_count(username) <= 1)

select dbo.username_count('a') as Count

insert t1 select 'a'
insert t2 select 'b'
insert t2 select 'a'
insert t1 select 'c'
insert t1 select 'b'

select dbo.username_count('a') as Count
select dbo.username_count('d') as Count

select * from t1
select * from t2

USE [master]
GO
/****** Object:  StoredProcedure [dbo].[ausp_UserNameCheck]    Script Date: 12/11/2009 11:07:17 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
 
ALTER PROCEDURE [dbo].[ausp_UserNameCheck] 
	-- Add the parameters for the stored procedure here
	@varUserName varchar(20)

AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	Declare @varUser varchar(20)
	Declare @varCountHR int
	Declare @varcountAgentLogin int
	
	Select @varCountHR = count(*) from [AdventureWorks].[dbo].[JohnsHR] where [AdventureWorks].[dbo].[JohnsHR].[UserName] = @varUserName
	Select @varCountAgentLogin = count(*) from [AdventureWorks].[dbo].[JohnsAgentLogin] where [AdventureWorks].[dbo].[JohnsAgentLogin].[UserId] = @varUserName

	if @varCountHR >= 1
		--Print 'ERROR!!  Duplicate username exists within the HR table.'
		RaisError('Error.  Duplicate Username exists with the HR table.', 16, 1)
	Else
		Print 'Username confirmed.  Inserting into HR table...'

	if @varCountAgentLogin >= 1
		--Print 'Error!  Duplicate username exists within the AgentLogin table.'
		RaisError('Error.  Duplicate Username exists with the AgentLogin table.', 16, 1)
	Else
		Print 'Username confirmed.  Inserting into AgentLogin table...'
		--Insert record into the AgentLogins table.
		
	
END

select	a.username
	from	agentlogin a
	join inserted i  <--
	on	i.username = a.username