创建触发器及函数

1、mysql创建触发器

sql = "CREATE TRIGGER `delkm` AFTER DELETE ON `td_kmdm` FOR EACH ROW begin "
????+ " delete from t_pyqx where t_pyqx.kmdm=old.kmdm;"
????+ " delete from td_klkmksb where td_klkmksb.kmdm=old.kmdm;"
????+ " delete from td_ycdm where td_ycdm.kmdm=old.kmdm;"
????+ " delete from t_jmxx where t_jmxx.kmdm=old.kmdm;"
????+ " delete from td_jxxxb where td_jxxxb.kmdm=old.kmdm;"
????+ " delete from t_stdfd where t_stdfd.kmdm=old.kmdm;"
????+ " delete from t_qydyb where t_qydyb.kmdm=old.kmdm;"
????+ " delete from t_xzdmb where t_xzdmb.kmdm=old.kmdm;"
????+ " delete from t_yhtemp where t_yhtemp.kmdm=old.kmdm;"
????+ " delete from td_qxzdm where td_qxzdm.kmdm=old.kmdm;"
????+ " end;";s

?

2、mysql创建函数

sql="create function substring_index(Str varchar(8000),StrSep varchar(10),AppPos int) " +
???"returns int " +
???"begin" +
???" declare i int;" +
???" declare ii int;" +
???" set Str=rtrim(ltrim(Str));" +
???" set i=1;" +
???" select charindex(StrSep,Str) into ii;" +
???" if i=AppPos then " +
???" return (ii);" +
???" else" +
???" test:while AppPos>i do " +
???" begin" +
???" if charindex(StrSep,right(Str,len(Str)-ii))<>0 then " +
???" select charindex(StrSep,len(Str)-ii))+ii into ii;" +
???" else" +
???" set ii=0;" +
???" set i=i+1;" +
???" end if;" +
???" end;" +
???" end while test;" +
???" end if;" +
???" return (ii );" +
???" end";

?

3、sqlserver创建触发器

sql = "CREATE TRIGGER delks ON td_ksdm for DELETE? as?? "
??????+ " delete t_pyqx from deleted?? a,t_pyqx b where a.ksdm=b.ksdm ;"
??????+ " delete? td_klkmksb from deleted?? a,td_klkmksb where td_klkmksb.ksdm=a.ksdm;"
??????+ " delete td_ycdm from deleted?? a,td_ycdm where td_ycdm.ksdm=a.ksdm;"
??????+ " delete t_jmxx from deleted?? a,t_jmxx where t_jmxx.ksdm=a.ksdm;"
??????+ " delete td_jxxxb from deleted?? a,td_jxxxb where td_jxxxb.ksdm=a.ksdm;"
??????+ " delete t_stdfd from deleted?? a,t_stdfd where t_stdfd.ksdm=a.ksdm;"
??????+ " delete t_qydyb from deleted?? a,t_qydyb where t_qydyb.ksdm=a.ksdm;"
??????+ " delete t_xzdmb from deleted?? a,t_xzdmb where t_xzdmb.ksdm=a.ksdm;"
??????+ " delete t_yhtemp from deleted?? a,t_yhtemp where t_yhtemp.ksdm=a.ksdm;"
??????+ " delete td_qxzdm from deleted?? a,td_qxzdm where td_qxzdm.ksdm=a.ksdm;";

?

4、sqlserver创建函数

sql="create function [dbo].[substring_index](@Str varchar(8000),@StrSep varchar(10),@AppPos int) " +???"returns int " +???"begin" +???" declare @i int" +???" declare @ii int" +???" set @Str=rtrim(ltrim(@Str))" +???" set @i=1" +???" select @ii=charindex(@StrSep,@Str)" +???" if @i=@AppPos" +???" return @ii" +???" else" +???" while @AppPos>@i" +???" begin" +???" if charindex(@StrSep,right(@Str,len(@Str)-@ii))<>0" +???" select @ii=charindex(@StrSep,len(@Str)-@ii))+@ii" +???" else" +???" set @ii=0" +???" set @i=@i+1" +???" end" +???" return @ii " +???"end";

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!