sybase数据库 级联删除

1.首先sybase不支持级联删除，可通过触发器来实现级联删除功能。

SYBASE产生触发器的语法为：
CREATE TRIGGER 触发器名
ON 表名
FOR INSERT，UPDATE，DELETE
AS
SQL_statement |
FOR INSERT，UPDATE AS
IF UPDATE(column_name) [AND|OR UPDATE(column_name)]...
SQL_statements
上面FOR子句用来指定在触发器上的哪些数据更新命令可激活该 触发器。
IF UPDATE子句检查对指定列的操作类型，在IF UPDATE子句 中可指定多个列。
与ORACLE不同，对于每条SQL语句，触发器只执行一次。
触发器在数据更新语句完成以后立即执行。触发器和启动它的语句被当作一个事务处理，事务可以在触发器中回退。

2.多表删除效率问题。

配置很多表，参考配置集CONFIGUREID作为外键，进行级联删除。目前在sybase只能使用触发器实现级联删除。
触发器同时删除90多张表时，效率过低。

通过以下方式可以解决该问题：
A.建表时，请使用“primary key nonclustered”关键字来建立表主键。
如只写“primary key”，sybase默认以“primary key clustered”方式建立表主键，这样删除时，数据库移动数据较大，会造成删除效率低。

alter table NR8250_R_DRYCTOUT
add constraint P_NR8250_R_DRYCTOUT primary key nonclustered (MOI)

B.建表时，请同时在该表级联删除时，where条件查找中需要匹配的ID索引，可以提高查找效率。
具体请参考下面的例子。


if exists (select * from sysobjects where id = object_id ('DBO.NR8250_R_SHELF') and sysstat & 0xf = 3)
drop table DBO.NR8250_R_SHELF
go
create table NR8250_R_SHELF
(MOI varchar(200) not null
,NEID varchar(200) not null
,UCRACKNO numeric not null
,UCSHELFNO numeric not null
,UCSHELFTYPE numeric null
,CONFIGUREID varchar(200) not null
)
----创建非聚簇主键
alter table NR8250_R_SHELF
add constraint P_NR8250_R_SHELF primary key nonclustered (MOI,UCRACKNO,UCSHELFNO)
----创建索引
create index IDX1_NR8250_R_SHELF on NR8250_R_SHELF(CONFIGUREID)

-----取消外键级联删除定义
/*alter table NR8250_R_SHELF
add constraint F_NR8250_R_SHELF foreign key (CONFIGUREID)
references NR8250_R_CONFIGURESET (CONFIGUREID) --on delete cascade*/
go

-------创建配置集以下表与配置集表的级联删除触发器
if exists(select * from sysobjects where name='TRG_DELETE_NR8250V1_CONFIGSET' and type='TR')
begin
drop trigger TRG_DELETE_NR8250V1_CONFIGSET
end

create trigger TRG_DELETE_NR8250V1_CONFIGSET
on NR8250_R_CONFIGURESET for delete
as
begin
---------多表级联删除
delete NR8250_R_RACK from deleted as d where NR8250_R_RACK.CONFIGUREID=d.CONFIGUREID
delete NR8250_R_SHELF from deleted as d where NR8250_R_SHELF.CONFIGUREID=d.CONFIGUREID
delete NR8250_R_BOARD from deleted as d where NR8250_R_BOARD.CONFIGUREID=d.CONFIGUREID
delete NR8250_R_SNTP from deleted as d where NR8250_R_SNTP.CONFIGUREID=d.CONFIGUREID
end

3.计算sql语句在sybase中的执行效率，
请在sql语句前后，增加SELECT CONVERT(VARCHAR(30),GETDATE(),109)语句，从前后时间差判定当前语句的执行效率。

SELECT CONVERT(VARCHAR(30),GETDATE(),109)
alter table NR8250_R_SHELF
add constraint P_NR8250_R_SHELF primary key nonclustered (MOI,UCRACKNO,UCSHELFNO)
SELECT CONVERT(VARCHAR(30),GETDATE(),109)
if exists (select * from sysobjects where id = object_id ('DBO.NR8250_R_SHELF') and sysstat & 0xf = 3)
drop table DBO.NR8250_R_SHELF
go
create table NR8250_R_SHELF
(MOI varchar(200) not null
,NEID varchar(200) not null
,UCRACKNO numeric not null
,UCSHELFNO numeric not null
,UCSHELFTYPE numeric null
,CONFIGUREID varchar(200) not null
)
----创建非聚簇主键
alter table NR8250_R_SHELF
add constraint P_NR8250_R_SHELF primary key nonclustered (MOI,UCRACKNO,UCSHELFNO)
----创建索引
create index IDX1_NR8250_R_SHELF on NR8250_R_SHELF(CONFIGUREID)

-----取消外键级联删除定义
/*alter table NR8250_R_SHELF
add constraint F_NR8250_R_SHELF foreign key (CONFIGUREID)
references NR8250_R_CONFIGURESET (CONFIGUREID) --on delete cascade*/
go


if exists (select * from sysobjects where id = object_id ('DBO.NR8250_R_RACK') and sysstat & 0xf = 3)
drop table DBO.NR8250_R_RACK
go
create table NR8250_R_RACK
(MOI varchar(200) not null
,NEID varchar(200) not null
,UCRACKNO numeric not null
,UCRACKTYPE numeric null
,ACRACKNAME varchar(200) null
,DWTANKNO numeric null
,DWRESERVED numeric null
,CONFIGUREID varchar(200) not null
)
alter table NR8250_R_RACK
add constraint P_NR8250_R_RACK primary key nonclustered (MOI,UCRACKNO)

create index IDX1_NR8250_R_RACK on NR8250_R_RACK(CONFIGUREID)
/*alter table NR8250_R_RACK
add constraint F_NR8250_R_RACK foreign key (CONFIGUREID)
references NR8250_R_CONFIGURESET (CONFIGUREID) --on delete cascade*/
go

if exists (select * from sysobjects where id = object_id ('DBO.NR8250_R_BOARD') and sysstat & 0xf = 3)
drop table DBO.NR8250_R_BOARD
go
create table NR8250_R_BOARD
(MOI varchar(200) not null
,NEID varchar(200) not null
,UCRACKNO numeric not null
,UCSHELFNO numeric not null
,UCSLOTNO numeric not null
,WBOARDTYPE numeric null
,UCPOWERCTRL numeric null
,UCMODE numeric null
--,DWRADIOMODE numeric null --NR8250 V2.00, BY QIUHUA 2012-03-05
--,DWFUNCSET numeric null --NR8250 V2.00, BY QIUHUA 2012-03-05
,DWRESV1 numeric null
,DWRESV2 numeric null
,DWRESV3 numeric null
,DWRESV4 numeric null
,DWRESV5 numeric null
,DWRESV6 numeric null
,DWRESV7 numeric null
,DWRESV8 numeric null
,DWRESV9 numeric null
,DWRESV10 numeric null
,DWMANUALOP numeric null
,DWSTATUS numeric null
,CONFIGUREID varchar(200) not null
)
alter table NR8250_R_BOARD
add constraint P_NR8250_R_BOARD primary key nonclustered (MOI,UCRACKNO,UCSHELFNO,UCSLOTNO)

create index IDX1_NR8250_R_BOARD on NR8250_R_BOARD(CONFIGUREID)

/*alter table NR8250_R_BOARD
add constraint F_NR8250_R_BOARD foreign key (CONFIGUREID)
references NR8250_R_CONFIGURESET (CONFIGUREID) --on delete cascade*/
go


-------创建配置集以下表与配置集表的级联删除触发器
if exists(select * from sysobjects where name='TRG_DELETE_NR8250V1_CONFIGSET' and type='TR')
begin
drop trigger TRG_DELETE_NR8250V1_CONFIGSET
end

create trigger TRG_DELETE_NR8250V1_CONFIGSET
on NR8250_R_CONFIGURESET for delete
as
begin
delete NR8250_R_RACK from deleted as d where NR8250_R_RACK.CONFIGUREID=d.CONFIGUREID
delete NR8250_R_SHELF from deleted as d where NR8250_R_SHELF.CONFIGUREID=d.CONFIGUREID
delete NR8250_R_BOARD from deleted as d where NR8250_R_BOARD.CONFIGUREID=d.CONFIGUREID
end