من این مثال رو با Power Designer درست کردم ولی تست نکردم. باید درست کار کنه:
/*================================================= =============*/
/* DBMS name: Microsoft SQL Server 2000 */
/* Created on: 3/18/2007 4:17:52 PM */
/*================================================= =============*/
if exists (select 1
from sysobjects
where id = object_id('Child')
and type = 'U')
drop table Child
go
if exists (select 1
from sysobjects
where id = object_id('Parent')
and type = 'U')
drop table Parent
go
/*================================================= =============*/
/* Table: Child */
/*================================================= =============*/
create table Child (
ParentID int null
)
go
/*================================================= =============*/
/* Table: Parent */
/*================================================= =============*/
create table Parent (
ParentID int identity,
constraint PK_PARENT primary key (ParentID)
)
go
/*================================================= =============*/
/* DBMS name: Microsoft SQL Server 2000 */
/* Created on: 3/18/2007 4:24:37 PM */
/*================================================= =============*/
if exists (select 1
from sysobjects
where id = object_id('Trigger_1')
and type = 'TR')
drop trigger Trigger_1
go
if exists (select 1
from sysobjects
where id = object_id('Trigger_1')
and type = 'TR')
drop trigger Trigger_1
go
if exists (select 1
from sysobjects
where id = object_id('Trigger_2')
and type = 'TR')
drop trigger Trigger_2
go
/* Insert trigger "Trigger_1" for table "Child" */
create trigger Trigger_1 on Child for insert as
begin
declare
@maxcard int,
@numrows int,
@numnull int,
@errno int,
@errmsg varchar(255)
select @numrows = @@rowcount
if @numrows = 0
return
/* Parent "Parent" must exist when inserting a child in "Child" */
if update(ParentID)
begin
select @numnull = (select count(*)
from inserted
where ParentID is null)
if @numnull != @numrows
if (select count(*)
from Parent t1, inserted t2
where t1.ParentID = t2.ParentID) != @numrows - @numnull
begin
select @errno = 50002,
@errmsg = 'Parent does not exist in "Parent". Cannot create child in "Child".'
goto error
end
end
return
/* Errors handling */
error:
raiserror @errno @errmsg
rollback transaction
end
go
/* Delete trigger "Trigger_1" for table "Parent" */
create trigger Trigger_1 on Parent for delete as
begin
declare
@numrows int,
@errno int,
@errmsg varchar(255)
select @numrows = @@rowcount
if @numrows = 0
return
/* Delete all children in "Child" */
delete Child
from Child t2, deleted t1
where t2.ParentID = t1.ParentID
return
/* Errors handling */
error:
raiserror @errno @errmsg
rollback transaction
end
go
/* Update trigger "Trigger_2" for table "Parent" */
create trigger Trigger_2 on Parent for update as
begin
declare
@maxcard int,
@numrows int,
@numnull int,
@errno int,
@errmsg varchar(255)
select @numrows = @@rowcount
if @numrows = 0
return
/* Modify parent code of "Parent" for all children in "Child" */
if update(ParentID)
begin
update Child
set ParentID = i1.ParentID
from Child t2, inserted i1, deleted d1
where t2.ParentID = d1.ParentID
and (i1.ParentID != d1.ParentID)
end
return
/* Errors handling */
error:
raiserror @errno @errmsg
rollback transaction
end
go
نوشته شده توسط AminSobati
