Методическое пособие 506
.pdfgo
ALTER TABLE Compliance
ADD FOREIGN KEY (NumberEquipment) REFERENCES Equipment
go
ALTER TABLE Compliance
ADD FOREIGN KEY (NumberShop) REFERENCES Shop
go
ALTER TABLE Coordination
ADD FOREIGN KEY (nomerOfEmployees) REFERENCES Employees
go
ALTER TABLE Coordination
ADD FOREIGN KEY (NumberDocuments) REFERENCES Documents
go
ALTER TABLE Counters
ADD FOREIGN KEY (NumberEquipment) REFERENCES Equipment
go
ALTER TABLE Documents
ADD FOREIGN KEY (NumberEquipment) REFERENCES Equipment
go
80
ALTER TABLE Documents
ADD FOREIGN KEY (Number_of_TO) REFERENCES Schedule_of_TI
go
ALTER TABLE Documents
ADD FOREIGN KEY (NumberShop) REFERENCES Shop
go
ALTER TABLE Documents
ADD FOREIGN KEY (nomerOfEmployees) REFERENCES Employees
go
ALTER TABLE Documents
ADD FOREIGN KEY (NumberOfOrganization) REFERENCES Organization
go
ALTER TABLE Employees
ADD FOREIGN KEY (NumberOfOrganization) REFERENCES Organization
go
ALTER TABLE Equipment
ADD FOREIGN KEY (NumberShop) REFERENCES Shop
go
81
ALTER TABLE Schedule_of_TI
ADD FOREIGN KEY (NumberEquipment) REFERENCES Equipment
go
ALTER TABLE Work_Log
ADD FOREIGN KEY (nomerOfEmployees) REFERENCES Employees
go
ALTER TABLE Work_Log
ADD FOREIGN KEY (Number_materials) REFERENCES Materials
go
ALTER TABLE Work_Log
ADD FOREIGN KEY (NumberEquipment) REFERENCES Equipment
go
create trigger tI_Compliance on Compliance for INSERT as /* ERwin Builtin Sat Apr 20 22:19:41 2019 */
/* INSERT trigger on Compliance */ begin
declare @numrows int, @nullcnt int, @validcnt int, @errno int,
@errmsg varchar(255)
82
select @numrows = @@rowcount
/* ERwin Builtin Sat Apr 20 22:19:41 2019 */
/* Documents R/40 Compliance ON CHILD INSERT SET NULL */
if
/* update(NumberDocuments) */ update(NumberDocuments)
begin
update Compliance set
/* Compliance.NumberDocuments = NULL */ Compliance.NumberDocuments = NULL
from Compliance,inserted where
/* */ 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 */
/* Equipment R/39 Compliance ON CHILD INSERT SET NULL */
if
/* update(NumberEquipment) */ update(NumberEquipment)
begin
update Compliance set
/* Compliance.NumberEquipment = NULL */ Compliance.NumberEquipment = NULL
83
from Compliance,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 */
/* Shop R/38 Compliance ON CHILD INSERT SET NULL */ if
/* update(NumberShop) */ update(NumberShop)
begin
update Compliance set
/* Compliance.NumberShop = NULL */ Compliance.NumberShop = NULL
from Compliance,inserted where
/* */ and
not exists (
select * from Shop where
/* inserted.NumberShop = Shop.NumberShop */ inserted.NumberShop = Shop.NumberShop
)
end
84
/* ERwin Builtin Sat Apr 20 22:19:41 2019 */ return
error:
raiserror @errno @errmsg rollback transaction
end go
create trigger tU_Compliance on Compliance for UPDATE as /* ERwin Builtin Sat Apr 20 22:19:41 2019 */
/* UPDATE trigger on Compliance */ begin
declare @numrows int, @nullcnt int, @validcnt int,
@insNumberCompliance int, @errno int,
@errmsg varchar(255)
select @numrows = @@rowcount
/* ERwin Builtin Sat Apr 20 22:19:41 2019 */
/* Documents R/40 Compliance ON CHILD UPDATE SET NULL */
if
/* update(NumberDocuments) */ update(NumberDocuments)
begin
update Compliance set
/* Compliance.NumberDocuments = NULL */ Compliance.NumberDocuments = NULL
from Compliance,inserted where
/* */ and
not exists (
select * from Documents
85
where
/* inserted.NumberDocuments = Documents.NumberDocuments */
inserted.NumberDocuments = Documents.NumberDocuments
)
end
/* ERwin Builtin Sat Apr 20 22:19:41 2019 */
/* Equipment R/39 Compliance ON CHILD UPDATE SET NULL */
if
/* update(NumberEquipment) */ update(NumberEquipment)
begin
update Compliance set
/* Compliance.NumberEquipment = NULL */ Compliance.NumberEquipment = NULL
from Compliance,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 */
/* Shop R/38 Compliance ON CHILD UPDATE SET NULL */ if
/* update(NumberShop) */
86
update(NumberShop) begin
update Compliance set
/* Compliance.NumberShop = NULL */ Compliance.NumberShop = NULL
from Compliance,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 */ return
error:
raiserror @errno @errmsg rollback transaction
end go
create trigger tI_Coordination on Coordination for INSERT as /* ERwin Builtin Sat Apr 20 22:19:41 2019 */
/* INSERT trigger on Coordination */ begin
declare @numrows int, @nullcnt int, @validcnt int, @errno int,
@errmsg varchar(255)
87
select @numrows = @@rowcount
/* ERwin Builtin Sat Apr 20 22:19:41 2019 */
/* Employees R/32 Coordination ON CHILD INSERT 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 INSERT SET NULL */
if
/* update(NumberDocuments) */ update(NumberDocuments)
begin
update Coordination set
/* Coordination.NumberDocuments = NULL */ Coordination.NumberDocuments = NULL
88
from Coordination,inserted where
/* */ 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 tU_Coordination on Coordination for UPDATE as /* ERwin Builtin Sat Apr 20 22:19:41 2019 */
/* UPDATE trigger on Coordination */ begin
declare @numrows int, @nullcnt int, @validcnt int,
@insNumberCoordination int, @errno int,
@errmsg varchar(255)
select @numrows = @@rowcount
/* ERwin Builtin Sat Apr 20 22:19:41 2019 */ 89