SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_CheckResourceQuotaGroup]') and xtype = N'P')
drop procedure [dbo].[sp_CheckResourceQuotaGroup]
GO

-- =============================================
-- Author:		Marat Zigangaraev
-- Create date: 2015/05/22
-- Description:	Проверка вставки квоты на группы для 
--              предотвращения участия одного ресурса в разных группах квот
-- =============================================
CREATE PROCEDURE [dbo].[sp_CheckResourceQuotaGroup] 
As
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for trigger here
	
if exists 
(
	select count(distinct g.ResourceQuotaGroupID)
	from [AKUZ].[T_RESOURCE_QUOTA_GROUP] g
	join [AKUZ].T_RESOURCE_QUOTA q On g.ResourceQuota = q.ResourceQuotaID
	join [AKUZ].T_RESOURCE_QUOTA_LINK s On q.ResourceQuotaLink = s.ResourceQuotaLinkID
	join [AKUZ].T_RESOURCE_GROUP_LINK l on g.ResourceGroup = l.ResourceGroup
	group by l.[Resource], s.[Resource], q.ResourceQuotaID 
	having count(distinct g.ResourceQuotaGroupID) > 1
)
	BEGIN
		RAISERROR ('В группе квоты существуют ресурсы, которые уже включены в группу квот.', 18, 1);
		RETURN 
	END
END
GO
