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
