CREATE TABLE [dbo].[ca_group_member]
(
[member_uuid] [binary] (16) NOT NULL,
[member_type] [smallint] NULL,
[group_domain_uuid] [binary] (16) NOT NULL,
[group_uuid] [binary] (16) NOT NULL,
[member_domain_uuid] [binary] (16) NOT NULL,
[last_update_date] [int] NULL,
[version_number] [int] NULL CONSTRAINT [DF__ca_group___versi__45A94D10] DEFAULT ((0)),
[auto_rep_version] [timestamp] NULL
) ON [PRIMARY]
GO
CREATE trigger ca_group_member_agent_delete
on ca_group_member
for delete
as
declare @member_uuid binary(16);
begin
DECLARE trans_Cursor CURSOR
FOR SELECT member_uuid
FROM deleted
OPEN trans_Cursor
FETCH NEXT FROM trans_Cursor INTO @member_uuid
WHILE @@FETCH_STATUS = 0
BEGIN
execute ca_agent_server_version_by_uuid @member_uuid;
FETCH NEXT FROM trans_Cursor INTO @member_uuid;
END
CLOSE trans_Cursor
DEALLOCATE trans_Cursor
end;
GO
CREATE trigger ca_group_member_agent_insert
on ca_group_member
after insert
as
declare @member_uuid binary(16);
begin
set @member_uuid = (select member_uuid from inserted);
execute ca_agent_server_version_by_uuid @member_uuid;
end;
GO
CREATE trigger r_iu_group_member
on ca_group_member
after insert, update
as
declare @_member_uuid binary(16);
declare @_group_uuid binary(16);
begin
select @_member_uuid =(select member_uuid from inserted);
select @_group_uuid = (select group_uuid from inserted);
execute p_iu_group_member @_member_uuid, @_group_uuid ;
end
GO
CREATE trigger r_u_ca_group_member
on ca_group_member
after update
as
if update(group_uuid)
begin
declare @member_uuid binary(16);
declare @old_group_uuid binary(16);
declare @new_group_uuid binary(16);
declare @new_group_domain_uuid binary(16);
declare @member_type integer;
declare @member_domain_uuid binary(16);
declare @old_group_domain_uuid binary(16);
declare cursor_ca_group_member cursor local for
select i.member_uuid, i.group_uuid, i.group_domain_uuid, i.member_type, i.member_domain_uuid,
d.group_uuid, d.group_domain_uuid from inserted i, deleted d
where i.member_uuid = d.member_uuid
open cursor_ca_group_member;
fetch next from cursor_ca_group_member into @member_uuid, @new_group_uuid, @new_group_domain_uuid, @member_type, @member_domain_uuid, @old_group_uuid, @old_group_domain_uuid;
while @@fetch_status = 0
begin
execute p_urc_ab_ca_group_member_updated @member_uuid, @old_group_uuid, @new_group_uuid,
@new_group_domain_uuid, @member_type, @member_domain_uuid, @old_group_domain_uuid;
fetch next from cursor_ca_group_member into @member_uuid, @new_group_uuid, @new_group_domain_uuid, @member_type, @member_domain_uuid, @old_group_uuid, @old_group_domain_uuid;
end;
close cursor_ca_group_member;
deallocate cursor_ca_group_member;
end
GO
CREATE trigger r_upd_verno_group_member
on ca_group_member
after update
as
declare @old_verno as int;
declare @new_verno as int;
begin
if update (version_number)
begin
DECLARE mycur CURSOR
FOR select deleted.version_number, inserted.version_number from inserted, deleted
OPEN mycur
FETCH NEXT FROM mycur INTO @old_verno,@new_verno
WHILE @@FETCH_STATUS = 0
BEGIN
execute p_integrity_version_number @old_verno , @new_verno ;
FETCH NEXT FROM mycur INTO @old_verno,@new_verno;
END
CLOSE mycur
DEALLOCATE mycur
end;
end;
GO
CREATE trigger r_urc_ab_ca_group_member_created
on ca_group_member
after insert
as
begin
declare @member_uuid binary(16);
declare @member_type integer;
declare @group_domain_uuid binary(16);
declare @group_uuid binary(16);
declare @member_domain_uuid binary(16);
set @member_uuid = (select member_uuid from inserted);
set @member_type = (select member_type from inserted);
set @group_domain_uuid = (select group_domain_uuid from inserted);
set @group_uuid = (select group_uuid from inserted);
set @member_domain_uuid = (select member_domain_uuid from inserted);
execute p_urc_ab_ca_group_member_created @member_uuid, @member_type, @group_domain_uuid, @group_uuid, @member_domain_uuid;
end
GO
CREATE trigger r_urc_ab_ca_group_member_deleted
on ca_group_member
after delete
as
begin
declare @member_uuid binary(16);
declare @member_type integer;
declare @group_uuid binary(16);
declare @group_domain_uuid binary(16);
declare cursor_urc_ab_ca_group_member cursor local for
select member_uuid, member_type, group_uuid, group_domain_uuid from deleted
open cursor_urc_ab_ca_group_member;
fetch next from cursor_urc_ab_ca_group_member into @member_uuid, @member_type, @group_uuid, @group_domain_uuid;
while @@fetch_status = 0
begin
execute p_urc_ab_ca_group_member_deleted @member_uuid, @member_type, @group_uuid, @group_domain_uuid;
fetch next from cursor_urc_ab_ca_group_member into @member_uuid, @member_type, @group_uuid, @group_domain_uuid;
end;
close cursor_urc_ab_ca_group_member;
deallocate cursor_urc_ab_ca_group_member;
end
GO
create trigger rule_d_so_removed_group_member
on ca_group_member
after delete
as
begin
declare @_object_uuid binary(16);
declare cur_del cursor
for select member_uuid
from deleted;
open cur_del;
fetch cur_del into @_object_uuid;
while @@fetch_status = 0
begin
execute proc_d_so_removed_group_member @_object_uuid;
fetch cur_del into @_object_uuid;
end;
close cur_del;
deallocate cur_del;
end;
GO
SET QUOTED_IDENTIFIER OFF
GO
create trigger rule_du_so_removed_group_member
on ca_group_member
after delete,update
as
begin
declare @_object_uuid binary(16);
declare cur_del cursor
for select member_uuid
from deleted;
open cur_del;
fetch cur_del into @_object_uuid;
while @@fetch_status = 0
begin
execute proc_d_so_removed_group_member @_object_uuid;
fetch cur_del into @_object_uuid;
end;
close cur_del;
deallocate cur_del;
end;
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE trigger rule_i_new_so_group_member
on ca_group_member
after insert
as
begin
declare @ngmem_uuid binary(16);
declare @nggroup_uuid binary(16);
select @ngmem_uuid = (Select member_uuid from inserted);
select @nggroup_uuid = (select group_uuid from inserted);
execute proc_i_new_so_group_member @ngmem_uuid, @nggroup_uuid;
end;
GO
create trigger usd_trg_d_ca_grp_tbl_ver
on ca_group_member
for delete as
declare
@i_member_type int
begin
set @i_member_type = (select top 1 member_type from deleted)
exec usd_proc_u_tbl_ver 3, -1, @i_member_type, -1
end
GO
CREATE trigger usd_trg_u_ca_grp_tbl_ver
on ca_group_member
for insert as
declare
@i_member_type int
begin
set @i_member_type = (select top 1 member_type from inserted)
exec usd_proc_u_tbl_ver 3, -1, @i_member_type, -1
end
GO
ALTER TABLE [dbo].[ca_group_member] ADD CONSTRAINT [XPKca_group_member] PRIMARY KEY CLUSTERED ([member_uuid], [group_uuid]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [ca_group_member_x1] ON [dbo].[ca_group_member] ([group_uuid], [member_uuid]) ON [PRIMARY]
GO
ALTER TABLE [dbo].[ca_group_member] ADD CONSTRAINT [$ca_gr_r000008a000000000] FOREIGN KEY ([group_uuid]) REFERENCES [dbo].[ca_group_def] ([group_uuid])
GO
GRANT SELECT ON [dbo].[ca_group_member] TO [ca_itrm_group]
GRANT INSERT ON [dbo].[ca_group_member] TO [ca_itrm_group]
GRANT DELETE ON [dbo].[ca_group_member] TO [ca_itrm_group]
GRANT UPDATE ON [dbo].[ca_group_member] TO [ca_itrm_group]
GRANT SELECT ON [dbo].[ca_group_member] TO [ca_itrm_group_ams]
GRANT SELECT ON [dbo].[ca_group_member] TO [dms_backup_group]
GRANT INSERT ON [dbo].[ca_group_member] TO [dms_backup_group]
GRANT DELETE ON [dbo].[ca_group_member] TO [dms_backup_group]
GRANT UPDATE ON [dbo].[ca_group_member] TO [dms_backup_group]
GRANT SELECT ON [dbo].[ca_group_member] TO [regadmin]
GRANT SELECT ON [dbo].[ca_group_member] TO [upmuser_group]
GO
[dbo].[ca_group_member]
![Foreign Keys $ca_gr_r000008a000000000: [dbo].[ca_group_def].group_uuid](../../Images/fk.gif)
