Методическое пособие 506
.pdf/* Employees R/32 Coordination ON CHILD UPDATE SET NULL */
if
/* update(nomerOfEmployees) */ update(nomerOfEmployees)
begin
update Coordination set
/* Coordination.nomerOfEmployees = NULL */ Coordination.nomerOfEmployees = NULL
from Coordination,inserted where
/* */ and
not exists (
select * from Employees where
/* inserted.nomerOfEmployees = Employees.nomerOfEmployees */
inserted.nomerOfEmployees = Employees.nomerOfEmployees
)
end
/* ERwin Builtin Sat Apr 20 22:19:41 2019 */
/* Documents R/31 Coordination ON CHILD UPDATE SET NULL */
if
/* update(NumberDocuments) */ update(NumberDocuments)
begin
update Coordination set
/* Coordination.NumberDocuments = NULL */ Coordination.NumberDocuments = NULL
from Coordination,inserted where
90
/* */ and
not exists (
select * from Documents where
/* inserted.NumberDocuments = Documents.NumberDocuments */
inserted.NumberDocuments = Documents.NumberDocuments
)
end
/* ERwin Builtin Sat Apr 20 22:19:41 2019 */ return
error:
raiserror @errno @errmsg rollback transaction
end go
create trigger tI_Counters on Counters for INSERT as /* ERwin Builtin Sat Apr 20 22:19:41 2019 */
/* INSERT trigger on Counters */ begin
declare @numrows int, @nullcnt int, @validcnt int, @errno int,
@errmsg varchar(255)
select @numrows = @@rowcount
/* ERwin Builtin Sat Apr 20 22:19:41 2019 */
/* Equipment R/52 Counters ON CHILD INSERT SET NULL */ if
/* update(NumberEquipment) */ update(NumberEquipment)
91
begin
update Counters set
/* Counters.NumberEquipment = NULL */ Counters.NumberEquipment = NULL
from Counters,inserted where
/* */ and
not exists (
select * from Equipment where
/* inserted.NumberEquipment = Equipment.NumberEquipment */
inserted.NumberEquipment = Equipment.NumberEquipment
)
end
/* ERwin Builtin Sat Apr 20 22:19:41 2019 */ return
error:
raiserror @errno @errmsg rollback transaction
end go
create trigger tU_Counters on Counters for UPDATE as /* ERwin Builtin Sat Apr 20 22:19:41 2019 */
/* UPDATE trigger on Counters */ begin
declare @numrows int, @nullcnt int, @validcnt int, @insN_counters int, @errno int,
@errmsg varchar(255)
92
select @numrows = @@rowcount
/* ERwin Builtin Sat Apr 20 22:19:41 2019 */
/* Equipment R/52 Counters ON CHILD UPDATE SET NULL */
if
/* update(NumberEquipment) */ update(NumberEquipment)
begin
update Counters set
/* Counters.NumberEquipment = NULL */ Counters.NumberEquipment = NULL
from Counters,inserted where
/* */ and
not exists (
select * from Equipment where
/* inserted.NumberEquipment = Equipment.NumberEquipment */
inserted.NumberEquipment = Equipment.NumberEquipment
)
end
/* ERwin Builtin Sat Apr 20 22:19:41 2019 */ return
error:
raiserror @errno @errmsg rollback transaction
end go
create trigger tD_Documents on Documents for DELETE as 93
/* ERwin Builtin Sat Apr 20 22:19:41 2019 */ /* DELETE trigger on Documents */
begin
declare @errno int, @errmsg varchar(255)
/* ERwin Builtin Sat Apr 20 22:19:41 2019 */
/* Documents R/40 Compliance ON PARENT DELETE SET NULL */
update Compliance set
/* Compliance.NumberDocuments = NULL */ Compliance.NumberDocuments = NULL
from Compliance,deleted where
/* Compliance.NumberDocuments = deleted.NumberDocuments */
Compliance.NumberDocuments = deleted.NumberDocuments
/* ERwin Builtin Sat Apr 20 22:19:41 2019 */
/* Documents R/31 Coordination ON PARENT DELETE SET NULL */
update Coordination set
/* Coordination.NumberDocuments = NULL */ Coordination.NumberDocuments = NULL
from Coordination,deleted where
/* Coordination.NumberDocuments = deleted.NumberDocuments */
Coordination.NumberDocuments = deleted.NumberDocuments
/* ERwin Builtin Sat Apr 20 22:19:41 2019 */ return
error:
94
raiserror @errno @errmsg rollback transaction
end go
create trigger tI_Documents on Documents for INSERT as /* ERwin Builtin Sat Apr 20 22:19:41 2019 */
/* INSERT trigger on Documents */ begin
declare @numrows int, @nullcnt int, @validcnt int, @errno int,
@errmsg varchar(255)
select @numrows = @@rowcount
/* ERwin Builtin Sat Apr 20 22:19:41 2019 */
/* Equipment R/45 Documents ON CHILD INSERT SET NULL */
if
/* update(NumberEquipment) */ update(NumberEquipment)
begin
update Documents set
/* Documents.NumberEquipment = NULL */ Documents.NumberEquipment = NULL
from Documents,inserted where
/* */ and
not exists (
select * from Equipment where
/* inserted.NumberEquipment = Equipment.NumberEquipment */
95
inserted.NumberEquipment = Equipment.NumberEquipment
)
end
/* ERwin Builtin Sat Apr 20 22:19:41 2019 */
/* Schedule_of_TI R/44 Documents ON CHILD INSERT SET NULL */
if
/* update(Number_of_TO) */ update(Number_of_TO)
begin
update Documents set
/* Documents.Number_of_TO = NULL */ Documents.Number_of_TO = NULL
from Documents,inserted where
/* */ and
not exists (
select * from Schedule_of_TI where
/* inserted.Number_of_TO = Schedule_of_TI.Number_of_TO */
inserted.Number_of_TO = Schedule_of_TI.Number_of_TO
)
end
/* ERwin Builtin Sat Apr 20 22:19:41 2019 */
/* Shop R/43 Documents ON CHILD INSERT SET NULL */ if
/* update(NumberShop) */ update(NumberShop)
begin
update Documents
96
set
/* Documents.NumberShop = NULL */ Documents.NumberShop = NULL
from Documents,inserted where
/* */ and
not exists (
select * from Shop where
/* inserted.NumberShop = Shop.NumberShop */ inserted.NumberShop = Shop.NumberShop
)
end
/* ERwin Builtin Sat Apr 20 22:19:41 2019 */
/* Employees R/21 Documents ON CHILD INSERT SET NULL */
if
/* update(nomerOfEmployees) */ update(nomerOfEmployees)
begin
update Documents set
/* Documents.nomerOfEmployees = NULL */ Documents.nomerOfEmployees = NULL
from Documents,inserted where
/* */ and
not exists (
select * from Employees where
/* inserted.nomerOfEmployees = Employees.nomerOfEmployees */
inserted.nomerOfEmployees = Employees.nomerOfEmployees
97
)
end
/* ERwin Builtin Sat Apr 20 22:19:41 2019 */
/* Organization R/20 Documents ON CHILD INSERT SET NULL */
if
/* update(NumberOfOrganization) */ update(NumberOfOrganization)
begin
update Documents set
/* Documents.NumberOfOrganization = NULL */ Documents.NumberOfOrganization = NULL
from Documents,inserted where
/* */ and
not exists (
select * from Organization where
/* inserted.NumberOfOrganization = Organization.NumberOfOrganization */
inserted.NumberOfOrganization = Organization.NumberOfOrganization
)
end
/* ERwin Builtin Sat Apr 20 22:19:41 2019 */ return
error:
raiserror @errno @errmsg rollback transaction
end go
98
create trigger tU_Documents on Documents for UPDATE as /* ERwin Builtin Sat Apr 20 22:19:41 2019 */
/* UPDATE trigger on Documents */ begin
declare @numrows int, @nullcnt int, @validcnt int,
@insNumberDocuments int, @errno int,
@errmsg varchar(255)
select @numrows = @@rowcount
/* ERwin Builtin Sat Apr 20 22:19:41 2019 */
/* Documents R/40 Compliance ON PARENT UPDATE SET NULL */
if
/* update(NumberDocuments) */ update(NumberDocuments)
begin
update Compliance set
/* Compliance.NumberDocuments = NULL */ Compliance.NumberDocuments = NULL
from Compliance,deleted where
/* Compliance.NumberDocuments = deleted.NumberDocuments */
Compliance.NumberDocuments = deleted.NumberDocuments
end
/* ERwin Builtin Sat Apr 20 22:19:41 2019 */
/* Documents R/31 Coordination ON PARENT UPDATE SET NULL */
if
/* update(NumberDocuments) */ update(NumberDocuments)
99