创建触发器及函数

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,right(Str,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,right(@Str,len(@Str)-@ii))+@ii" +   " else" +   " set @ii=0" +   " set @i=@i+1" +   " end" +   " return @ii " +   "end";