CREATE trigger dbo.TR_PDACCOUNTSTRUCTURE_VALIDATE on dbo.PDACCOUNTSTRUCTURE
for insert, update not for replication as
begin
set nocount on;
declare @ISBASICGL bit, @PDACCOUNTSYSTEMID uniqueidentifier
select top 1 @ISBASICGL = ISBASICGL, @PDACCOUNTSYSTEMID = PDACCOUNTSYSTEMID from inserted
if @ISBASICGL = 0 and (select count(*) from INSERTED) > 0
begin try
declare @COUNT int;
if exists(select 1 from inserted where not ((charindex('*',DESCRIPTION) = 0) and (charindex('?',DESCRIPTION) = 0)))
begin
raiserror('CK_PDACCOUNTSTRUCTURE_DESCRIPTIONVALIDCHARACTERS', 16, 1)
rollback
end
-- Validate total number of elements
select @COUNT = COUNT(1)
from dbo.PDACCOUNTSTRUCTURE ST
full outer join inserted T1 on ST.ID = T1.ID
where T1.PDACCOUNTSYSTEMID = @PDACCOUNTSYSTEMID or ST.PDACCOUNTSYSTEMID = @PDACCOUNTSYSTEMID
if @COUNT > 30
raiserror ('ERR_PDACCOUNTSTRUCTURE_NUMBEROFELEMENTS',13,1);
-- Validate total length, including separators, -1 for last separator
select @COUNT = SUM(case when T1.ID is null then ST.[LENGTH] else T1.[LENGTH] end) + count(*) - 1
from dbo.PDACCOUNTSTRUCTURE ST
full outer join inserted T1 on ST.ID = T1.ID
where (T1.PDACCOUNTSYSTEMID = @PDACCOUNTSYSTEMID or ST.PDACCOUNTSYSTEMID = @PDACCOUNTSYSTEMID)
and ((case when T1.ID is null then ST.ELEMENTTYPECODE else T1.ELEMENTTYPECODE end) = 1)
if @COUNT > 100
raiserror ('ERR_PDACCOUNTSTRUCTURE_TOTALLENGTH',13,1);
end try
begin catch
exec dbo.USP_RAISE_ERROR
end catch
end
|
Trigger Definition
