In Sybase 11, triggers are needed to complement the declarative referential integrity constraints (foreign key) specifications only for referential integrity constraints associated with cascades, nullifies or multi-target delete rules. Sybase 11 trigger procedures generated from the OPM schema shown in section 3 are contained in file Example_ritriggers.SYB11; we show below only part of this file, namely the procedures associated with the relations representing object class COURSE of the OPM schema shown in section 3.
use Example
go
create trigger iCOURSE on COURSE
for insert as
begin
declare @num1 int
if ( @@rowcount = 0 ) return
select @num1 = count(*) from inserted
where not exists (select * from OBJECTS
where inserted._oid = OBJECTS._oid)
if (@num1 != 0)
begin
raiserror 70001 "Cannot insert into COURSE because of
missing reference to OBJECTS"
print "Missing reference(s):"
select * from inserted
where not exists (select * from OBJECTS
where inserted._oid = OBJECTS._oid)
rollback trigger
return
end
select @num1 = count(*) from inserted
where inserted._defaultVersion is not null
and not exists (select * from _vCOURSE
where inserted._oid = _vCOURSE._oid
and inserted._defaultVersion = _vCOURSE._version)
if (@num1 != 0)
begin
raiserror 70001 "Cannot insert into COURSE because of
missing reference to _vCOURSE"
print "Missing reference(s):"
select * from inserted
where inserted._defaultVersion is not null
and not exists (select * from _vCOURSE
where inserted._oid = _vCOURSE._oid
and inserted._defaultVersion = _vCOURSE._version)
rollback trigger
return
end
end
go
create trigger dCOURSE on COURSE
for delete as
begin
declare @num1 int
if ( @@rowcount = 0 ) return
delete _vCOURSE
from deleted, _vCOURSE
where deleted._oid = _vCOURSE._oid
if ( @@error != 0 )
begin
raiserror 70006 "cannot perform cascade delete from COURSE to _vCOURSE"
rollback trigger
return
end
end
go
create trigger uCOURSE on COURSE
for update as
begin
declare @num1 int
if ( @@rowcount = 0 ) return
if update(_oid)
begin
select @num1 = count(*) from inserted
where not exists (select * from OBJECTS
where inserted._oid = OBJECTS._oid)
if (@num1 != 0)
begin
raiserror 70001 "Cannot insert into COURSE because of
missing reference to OBJECTS"
print "Missing reference(s):"
select * from inserted
where not exists (select * from OBJECTS
where inserted._oid = OBJECTS._oid)
rollback trigger
return
end
end
if update(_oid) or update(_defaultVersion)
begin
select @num1 = count(*) from inserted
where inserted._defaultVersion is not null
and not exists (select * from _vCOURSE
where inserted._oid = _vCOURSE._oid
and inserted._defaultVersion = _vCOURSE._version)
if (@num1 != 0)
begin
raiserror 70001 "Cannot insert into COURSE because of
missing reference to _vCOURSE"
print "Missing reference(s):"
select * from inserted
where inserted._defaultVersion is not null
and not exists (select * from _vCOURSE
where inserted._oid = _vCOURSE._oid
and inserted._defaultVersion = _vCOURSE._version)
rollback trigger
return
end
end
if update(_oid)
begin
if ( ( select count(*)
from deleted, _vCOURSE
where deleted._oid = _vCOURSE._oid ) !=
( select count(*)
from deleted, inserted, _vCOURSE
where deleted._oid = _vCOURSE._oid
and inserted._oid = _vCOURSE._oid ) )
begin
raiserror 70002 "Cannot delete from COURSE because of
existing reference from _vCOURSE"
print "Instance(s) causing violation:"
select * from deleted
where exists (select * from _vCOURSE
where deleted._oid = _vCOURSE._oid)
and not exists
(select * from inserted
where deleted._oid = inserted._oid)
rollback trigger
return
end
end
end
go
create trigger iCOURSE_references on COURSE_references
for insert as
begin
declare @num1 int
if ( @@rowcount = 0 ) return
select @num1 = count(*) from inserted
where not exists (select * from _vCOURSE
where inserted._oid = _vCOURSE._oid
and inserted._version = _vCOURSE._version)
if (@num1 != 0)
begin
raiserror 70001 "Cannot insert into COURSE_references because of
missing reference to _vCOURSE"
print "Missing reference(s):"
select * from inserted
where not exists (select * from _vCOURSE
where inserted._oid = _vCOURSE._oid
and inserted._version = _vCOURSE._version)
rollback trigger
return
end
select @num1 = count(*) from inserted
where not exists (select * from REFERENCE
where inserted.references = REFERENCE._oid)
if (@num1 != 0)
begin
raiserror 70001 "Cannot insert into COURSE_references because of
missing reference to REFERENCE"
print "Missing reference(s):"
select * from inserted
where not exists (select * from REFERENCE
where inserted.references = REFERENCE._oid)
rollback trigger
return
end
end
go
create trigger uCOURSE_references on COURSE_references
for update as
begin
declare @num1 int
if ( @@rowcount = 0 ) return
if update(_oid) or update(_version)
begin
select @num1 = count(*) from inserted
where not exists (select * from _vCOURSE
where inserted._oid = _vCOURSE._oid
and inserted._version = _vCOURSE._version)
if (@num1 != 0)
begin
raiserror 70001 "Cannot insert into COURSE_references because of
missing reference to _vCOURSE"
print "Missing reference(s):"
select * from inserted
where not exists (select * from _vCOURSE
where inserted._oid = _vCOURSE._oid
and inserted._version = _vCOURSE._version)
rollback trigger
return
end
end
if update(references)
begin
select @num1 = count(*) from inserted
where not exists (select * from REFERENCE
where inserted.references = REFERENCE._oid)
if (@num1 != 0)
begin
raiserror 70001 "Cannot insert into COURSE_references because of
missing reference to REFERENCE"
print "Missing reference(s):"
select * from inserted
where not exists (select * from REFERENCE
where inserted.references = REFERENCE._oid)
rollback trigger
return
end
end
end
go
create trigger i_vCOURSE on _vCOURSE
for insert as
begin
declare @num1 int
if ( @@rowcount = 0 ) return
select @num1 = count(*) from inserted
where not exists (select * from COURSE
where inserted._oid = COURSE._oid)
if (@num1 != 0)
begin
raiserror 70001 "Cannot insert into _vCOURSE because of
missing reference to COURSE"
print "Missing reference(s):"
select * from inserted
where not exists (select * from COURSE
where inserted._oid = COURSE._oid)
rollback trigger
return
end
end
go
create trigger d_vCOURSE on _vCOURSE
for delete as
begin
declare @num1 int
if ( @@rowcount = 0 ) return
select @num1 = count(*)
from deleted, COURSE
where deleted._oid = COURSE._oid
and deleted._version = COURSE._defaultVersion
if (@num1 != 0)
begin
raiserror 70002 "Cannot delete from _vCOURSE because of
existing reference from COURSE"
print "Instance(s) causing violation:"
select * from deleted, COURSE
where deleted._oid = COURSE._oid
and deleted._version = COURSE._defaultVersion
rollback trigger
return
end
delete COURSE_references
from deleted, COURSE_references
where deleted._oid = COURSE_references._oid
and deleted._version = COURSE_references._version
if ( @@error != 0 )
begin
raiserror 70006 "cannot perform cascade delete from _vCOURSE to COURSE_references"
rollback trigger
return
end
select @num1 = count(*)
from deleted, STUDENT_attends
where deleted._oid = STUDENT_attends.course
and deleted._version = STUDENT_attends._vcourse
if (@num1 != 0)
begin
raiserror 70002 "Cannot delete from _vCOURSE because of
existing reference from STUDENT_attends"
print "Instance(s) causing violation:"
select * from deleted, STUDENT_attends
where deleted._oid = STUDENT_attends.course
and deleted._version = STUDENT_attends._vcourse
rollback trigger
return
end
end
go
create trigger u_vCOURSE on _vCOURSE
for update as
begin
declare @num1 int
if ( @@rowcount = 0 ) return
if update(_oid)
begin
select @num1 = count(*) from inserted
where not exists (select * from COURSE
where inserted._oid = COURSE._oid)
if (@num1 != 0)
begin
raiserror 70001 "Cannot insert into _vCOURSE because of
missing reference to COURSE"
print "Missing reference(s):"
select * from inserted
where not exists (select * from COURSE
where inserted._oid = COURSE._oid)
rollback trigger
return
end
end
if update(_oid) or update(_version)
begin
if ( ( select count(*)
from deleted, COURSE
where deleted._oid = COURSE._oid
and deleted._version = COURSE._defaultVersion ) !=
( select count(*)
from deleted, inserted, COURSE
where deleted._oid = COURSE._oid
and inserted._oid = COURSE._oid
and deleted._version = COURSE._defaultVersion
and inserted._version = COURSE._defaultVersion ) )
begin
raiserror 70002 "Cannot delete from _vCOURSE because of
existing reference from COURSE"
print "Instance(s) causing violation:"
select * from deleted
where exists (select * from COURSE
where deleted._oid = COURSE._oid
and deleted._version = COURSE._defaultVersion)
and not exists
(select * from inserted
where deleted._oid = inserted._oid
and deleted._version = inserted._version)
rollback trigger
return
end
end
if update(_oid) or update(_version)
begin
if ( ( select count(*)
from deleted, COURSE_references
where deleted._oid = COURSE_references._oid
and deleted._version = COURSE_references._version ) !=
( select count(*)
from deleted, inserted, COURSE_references
where deleted._oid = COURSE_references._oid
and inserted._oid = COURSE_references._oid
and deleted._version = COURSE_references._version
and inserted._version = COURSE_references._version ) )
begin
raiserror 70002 "Cannot delete from _vCOURSE because of
existing reference from COURSE_references"
print "Instance(s) causing violation:"
select * from deleted
where exists (select * from COURSE_references
where deleted._oid = COURSE_references._oid
and deleted._version = COURSE_references._version)
and not exists
(select * from inserted
where deleted._oid = inserted._oid
and deleted._version = inserted._version)
rollback trigger
return
end
end
if update(_oid) or update(_version)
begin
if ( ( select count(*)
from deleted, STUDENT_attends
where deleted._oid = STUDENT_attends.course
and deleted._version = STUDENT_attends._vcourse ) !=
( select count(*)
from deleted, inserted, STUDENT_attends
where deleted._oid = STUDENT_attends.course
and inserted._oid = STUDENT_attends.course
and deleted._version = STUDENT_attends._vcourse
and inserted._version = STUDENT_attends._vcourse ) )
begin
raiserror 70002 "Cannot delete from _vCOURSE because of
existing reference from STUDENT_attends"
print "Instance(s) causing violation:"
select * from deleted
where exists (select * from STUDENT_attends
where deleted._oid = STUDENT_attends.course
and deleted._version = STUDENT_attends._vcourse)
and not exists
(select * from inserted
where deleted._oid = inserted._oid
and deleted._version = inserted._version)
rollback trigger
return
end
end
end
go
quit