CREATE trigger dbo.TR_MEMBER_VALIDMEMBERCHECK on dbo.MEMBER after insert, update not for replication
as begin
set nocount on;
if update(CONSTITUENTID) or update(MEMBERSHIPID) or update(ISDROPPED)
begin
if exists (
select top 1
1
from
dbo.MEMBER
inner join (
select distinct MEMBERSHIPID, CONSTITUENTID
from inserted
where ISDROPPED = 0
) as UPDATEDMEMBERS on UPDATEDMEMBERS.MEMBERSHIPID = MEMBER.MEMBERSHIPID and UPDATEDMEMBERS.CONSTITUENTID = MEMBER.CONSTITUENTID
where
MEMBER.ISDROPPED = 0
group by
MEMBER.MEMBERSHIPID, MEMBER.CONSTITUENTID
having
count(*) > 1
)
begin
raiserror('BBERR_INVALIDMEMBER', 13, 1);
rollback transaction;
end
if exists (
select
1
from
inserted
inner join
dbo.MEMBERSHIP as INSERTEDMEMBERSHIP on INSERTEDMEMBERSHIP.ID = inserted.MEMBERSHIPID
inner join
dbo.MEMBERSHIPPROGRAM on MEMBERSHIPPROGRAM.ID = INSERTEDMEMBERSHIP.MEMBERSHIPPROGRAMID
where
inserted.ISDROPPED = 0
and MEMBERSHIPPROGRAM.ALLOWMULTIPLEMEMBERSHIPS = 0
and INSERTEDMEMBERSHIP.STATUSCODE <> 1 -- Cancelled
and exists (
select
1
from
dbo.MEMBER
inner join
dbo.MEMBERSHIP on MEMBERSHIP.ID = MEMBER.MEMBERSHIPID
where
MEMBER.CONSTITUENTID = inserted.CONSTITUENTID
and MEMBER.MEMBERSHIPID <> inserted.MEMBERSHIPID
and MEMBERSHIP.MEMBERSHIPPROGRAMID = INSERTEDMEMBERSHIP.MEMBERSHIPPROGRAMID
and MEMBERSHIP.STATUSCODE <> 1 -- Cancelled
and MEMBER.ISDROPPED = 0
)
)
begin
raiserror('CK_MEMBER_VALIDALLOWMULTIPLEMEMBERSHIPS', 13, 1);
rollback transaction;
end
end
end
|
Trigger Definition
