金沙国际官网_金沙国际平台登录

因为这个金沙国际官网_金沙国际平台登录网站与很多的大型澳门赌场都有合作,金沙国际官网_金沙国际平台登录尽职尽责,高效执行,保持好奇心,不断学习,追求卓越,点击进入金沙国际官网_金沙国际平台登录马上体验吧,所以现在也正式地开始了营业。

您的位置:金沙国际官网 > 数据库 > Mysql数据库的增删改查,隐式事务

Mysql数据库的增删改查,隐式事务

发布时间:2019-11-03 06:16编辑:数据库浏览(178)

    这里讲讲SqlServer的隐式事务。(英文Implicit:含蓄的;暗示的;盲从的;)

    在运行程序之前需要在mysql数据库中创建test数据库,如下图所示:图片 1

    1 建立备份数据表

    图片 2图片 3

    CREATE TABLE [dbo].[ProcBackup](
        [id] [int] IDENTITY(1,1) NOT NULL,
        [name] [sysname] NOT NULL,
        [db] [nvarchar](50) NULL,
        [obj_id] [int] NULL,
        [create_date] [datetime] NOT NULL,
        [modify_date] [datetime] NOT NULL,
        [text] [nvarchar](4000) NULL,
        [type] [nvarchar](5) NULL,
        [remark] [nvarchar](500) NULL,
        [backup_date] [datetime] NULL
    ) ON [PRIMARY]
    
    GO
    
    EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'自增ID' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'ProcBackup', @level2type=N'COLUMN',@level2name=N'id'
    GO
    
    EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'名称' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'ProcBackup', @level2type=N'COLUMN',@level2name=N'name'
    GO
    
    EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'所在数据库' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'ProcBackup', @level2type=N'COLUMN',@level2name=N'db'
    GO
    
    EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'系统对象id' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'ProcBackup', @level2type=N'COLUMN',@level2name=N'obj_id'
    GO
    
    EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'创建时间' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'ProcBackup', @level2type=N'COLUMN',@level2name=N'create_date'
    GO
    
    EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'修改时间' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'ProcBackup', @level2type=N'COLUMN',@level2name=N'modify_date'
    GO
    
    EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'内容' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'ProcBackup', @level2type=N'COLUMN',@level2name=N'text'
    GO
    
    EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'类型' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'ProcBackup', @level2type=N'COLUMN',@level2name=N'type'
    GO
    
    EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'备注' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'ProcBackup', @level2type=N'COLUMN',@level2name=N'remark'
    GO
    
    EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'记录时间' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'ProcBackup', @level2type=N'COLUMN',@level2name=N'backup_date'
    GO
    

    ProcBackup

    隐式事务无需显示开始:不用 Begin Transaction,必须显示结束:以 Commit Transaction 或者 Rollback Transaction 方式结束。

    下面是具体是实现程序:

    2 创建存储过程

    图片 4图片 5

    create proc proc_backup
    as
    --插入新增的存储过程
    insert into ProcBackup 
    select A.name,'db_' as db,A.[object_id] as obj_id,A.create_date,A.modify_date,C.[text],'P' as type,'' as remark,getdate() as backup_date from sys.procedures A 
    left join sys.syscomments C on A.[object_id] = C.id 
    where A.name not in (select name from ProcBackup) 
    
    --插入修改过的存储过程
    insert into ProcBackup 
    select A.name,'db_' as db,A.[object_id] as obj_id,A.create_date,A.modify_date,C.[text],'P' as type,'' as remark,getdate() as backup_date from sys.procedures A 
    left join ProcBackup B on A.[object_id] = B.obj_id 
    left join sys.syscomments C on A.[object_id] = C.id 
    where A.modify_date > B.modify_date
    
    --插入新增的视图
    insert into ProcBackup 
    select A.name,'db_' as db,A.[object_id] as obj_id,A.create_date,A.modify_date,C.[text],'V' as type,'' as remark,getdate() as backup_date from sys.views A 
    left join sys.syscomments C on A.[object_id] = C.id 
    where A.name not in (select name from ProcBackup) 
    
    --插入修改过的视图
    insert into ProcBackup 
    select A.name,'db_' as db,A.[object_id] as obj_id,A.create_date,A.modify_date,C.[text],'V' as type,'' as remark,getdate() as backup_date from sys.views A 
    left join ProcBackup B on A.[object_id] = B.obj_id 
    left join sys.syscomments C on A.[object_id] = C.id 
    where A.modify_date > B.modify_date
    

    proc_backup

    本文由金沙国际官网发布于数据库,转载请注明出处:Mysql数据库的增删改查,隐式事务

    关键词:

上一篇:没有了

下一篇:没有了