烦人的sybase

最新推荐文章于 2019-04-23 11:48:53 发布

原创最新推荐文章于 2019-04-23 11:48:53 发布 · 428 阅读

0 ·

CC 4.0 BY-SA版权

数据库Oracle 专栏收录该内容

8 篇文章

订阅专栏

本文介绍了一个具体的SQL视图创建示例，并展示了如何使用SQL创建触发器。此外，还提供了检查表、触发器及视图是否存在的SQL查询语句。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

1：概述

听说快要被淘汰了，网上的资料也很少。

2：注意点

大小写非常敏感，sql语法和sqlserver差不多

3：sql举例，备忘

--创建视图 v_cdr_madz

create view FF
as
  select DA.ID as patientid,
               DA.MZHM as clinicid,
               DA.SFZH as idcard,
               case when DA.SFZH is null then '' else '01' end as idcardcode,
               '-' as healthcardid,
               '-' as healthinsurancecardid,
               DA.BRXM as name,
               DA.CSNY as birthdatetime,
               case when (DA.BRXB = 1 or DA.BRXB = 2) then DA.BRXB else 9 end as sex,
               case when (DA.HYZK < 1 or DA.HYZK > 4 or DA.HYZK is null) then 9 else DA.HYZK end as maritalstatus
    from MS_BRDA as DA

创建触发器

CREATE  TRIGGER TR_CDR_MS_BRDA
  ON MS_BRDA
  FOR INSERT,UPDATE,DELETE
AS
BEGIN
     DECLARE @V_SOURCEID NUMERIC(30)
     DECLARE @C_LOGTYPE  CHAR(1)
 
     IF EXISTS(SELECT 1 FROM inserted)
          BEGIN
               SELECT @V_SOURCEID = ID FROM inserted
               IF EXISTS(SELECT 1 FROM deleted)
              
                    SET @C_LOGTYPE='3'
               ELSE
                   
                    SET @C_LOGTYPE='1'
          END
     ELSE
         
          BEGIN
                 SELECT @V_SOURCEID = ID FROM deleted
               SET @C_LOGTYPE='2'
          END    
    
IF (@V_SOURCEID IS NOT NULL)
     INSERT INTO LOG_OV_PATIENTBASICINFO (LOGTYPE,LOGTIME,SOURCETABLE,SOURCEID,MASTERTABLEFLAG)
     VALUES (@C_LOGTYPE,GETDATE(),'MS_BRDA',@V_SOURCEID,'1')
END

注意：不能有分号
1：表是否存在

select  count(*)  from sysobjects where name='{0}' and type='U'

2：触发器是否存在

select count(*)  from sysobjects where name='{0}' and type='TR'

3：视图是否存在

select count(*) from sysobjects where name = '{0}'  and type = 'V'

4：得到触发器的创建脚本

select m.text from sysobjects o,syscomments m where  o.type='TR' and o.name='{0}' and o.id=m.id

5：得到视图的创建脚本

select m.text from sysobjects o,syscomments m where  o.type='V' and o.name='{0}' and o.id=m.id"