精华内容
下载资源
问答
  • 触发器中Before与After区别
    2019-10-12 18:32:32

    基本概念:
    after:是先完成数据的增删改,然后再触发,触发的语句晚于监视的增删改,无法影响前面的增删改动作;也就是说先插入订单记录,再更新商品数量。当商品数量少于订单数量时造成爆库。
    before:先完成触发,在进行增删改,触发语句先于监视的增删改,我们就有机会判断,修改即将发生的操作。如:我们在触发之前需要判断new值和old值的大小或关系,如果满足要求就触发,不通过就修改再触发;如:表之间定义的有外键,在删除主键时,必须要先删除外键表,这时就有先后之分,这里before相当于设置了断点,我们可以处理删除外键。

    Before与After区别:

    before:(insert、update)可以对new进行修改,after不能对new进行修改,两者都不能修改old数据。
    对于INSERT语句, 只有NEW是合法的;

    对于DELETE语句,只有OLD才合法;

    对于UPDATE语句,NEW、OLD可以同时使用。

    更多相关内容
  • <div><p>该提问来源于开源项目:GoogleCloudPlatform/compute-image-tools</p></div>
  • mysql触发器Before和After的区别

    万次阅读 2012-03-24 12:41:05
    Before与After区别:before:(insert、update)可以对new进行修改。  after不能对new进行修改。  两者都不能修改old数据。   触发器,顾名思义,就是当执行某个事件的时候触发另一个事件的执行!这个触发的...

    Before与After区别:before:(insert、update)可以对new进行修改。

                       after不能对new进行修改。

                       两者都不能修改old数据。

     

    触发器,顾名思义,就是当执行某个事件的时候触发另一个事件的执行!这个触发的时间分为BEFORE和AFTER两种,分别有before insert,before update,before delete和after insert,after update,after delete这六种情况

    以下的文章主要向大家讲述的是MySQL触发器的实际使用详细说明与实际案例分析,同时本文也列举了一些在MySQL触发器的实际式操作中的代码,以下就是文章的详细内容介绍,望大家借鉴。原文地址:http://database.51cto.com/art/201005/200671.htm

    FOR EACH ROW 子句通知触发器每隔一行执行一次动作,而不是对整个表,在触发器中我们经常会用到NEWOLD这两个关键字,在这里我还是先解释下这两个字的意思!其中NEW是指刚刚插入到数据库的数据表中的一个域,也可以理解为对象(object)的意思。比如在一个insert触发器中我用到了NEW那他的意思就是说,要使用刚刚插入到数据表的某个值,要使用具体的某一个值的话需要这样子写:NEW.字段名(NEW.username)这样子就获取到了刚刚插入到数据表中的一个字段的值!!OLD,OLD是在delete触发器中有用的,意思是说我要删除一条数据,但是在触发器中要用到之前的那个数据,那么我就可以通过OLD来获取了!比如我删除了一条数据,里面包含一个email,现在我在触发器的内部sql中要用就可以这样子写:OLD.email!

    对于INSERT语句, 只有NEW是合法的;对于DELETE语句,只有OLD才合法;而UPDATE语句可以在
    和NEW以及OLD同时使用

     

    下面举例说明:

    mysql> select * from t1;            
    +----+------+
    | id | name |
    +----+------+
    |  1 | aa   |
    +----+------+
    1 row in set (0.00 sec)

    mysql> select * from t2;
    Empty set (0.00 sec)

     

    before触发器代码:

    DELIMITER $$

    USE `test`$$

    DROP TRIGGER /*!50032 IF EXISTS */ `t1_before`$$

    CREATE
        /*!50017 DEFINER = 'admin'@'%' */
        TRIGGER `t1_before` BEFORE UPDATE ON `t1`
        FOR EACH ROW
        BEGIN


        set NEW.id=NEW.id+11;


        set NEW.name=UPPER(NEW.name);


        INSERT INTO t2 VALUES (NEW.id,NEW.name);
       

        END$$

    DELIMITER ;

     

     

     

     

     

     

     

     

     

     

     

     

     

     

    mysql> update t1 set name='bbs' where id=1;
    Query OK, 1 row affected (0.00 sec)
    Rows matched: 1  Changed: 1  Warnings: 0

    mysql> select * from t1;                  
    +----+------+
    | id | name |
    +----+------+
    | 12 | BBS  |
    +----+------+
    1 row in set (0.00 sec)

    mysql> select * from t2;
    +----+------+
    | id | name |
    +----+------+
    | 12 | BBS  |
    +----+------+
    1 row in set (0.00 sec)


    NEW值,已经被修改。

    ========================================================================

    after触发器代码:

     

    DELIMITER $$

    USE `test`$$

    DROP TRIGGER /*!50032 IF EXISTS */ `t1_after`$$

    CREATE
        /*!50017 DEFINER = 'admin'@'%' */
        TRIGGER `t1_after` AFTER UPDATE ON `t1`
        FOR EACH ROW
        BEGIN

        set NEW.id=NEW.id+11;

        set NEW.name=UPPER(NEW.name);

        INSERT INTO t2 VALUES (NEW.id,NEW.name);

        END$$


    DELIMITER ;

     

    把before触发器删除,在重新建立触发器的时候会报错,报错信息如下:

    由于是after触发器,不能对NEW值修改,所以报错。

     

    所以,after触发器就改成了如下:

     

    DELIMITER $$

    USE `test`$$

    DROP TRIGGER /*!50032 IF EXISTS */ `t1_after`$$

    CREATE
        /*!50017 DEFINER = 'admin'@'%' */
        TRIGGER `t1_after` AFTER UPDATE ON `t1`
        FOR EACH ROW
        BEGIN

        -- set NEW.id=NEW.id+11;

        -- set NEW.name=UPPER(NEW.name);

        INSERT INTO t2 VALUES (OLD.id,OLD.name);

        END$$


    DELIMITER ;

     

    mysql> select * from t1;
    +----+------+
    | id | name |
    +----+------+
    |  1 | aa   |
    +----+------+
    1 row in set (0.00 sec)

    mysql> select * from t2;
    Empty set (0.00 sec)

    mysql> update t1 set name='bbs' where id=1;
    Query OK, 1 row affected (0.00 sec)
    Rows matched: 1  Changed: 1  Warnings: 0

    mysql> select * from t1;                  
    +----+------+
    | id | name |
    +----+------+
    |  1 | bbs  |
    +----+------+
    1 row in set (0.00 sec)

    mysql> select * from t2;                  
    +----+------+
    | id | name |
    +----+------+
    |  1 | aa   |
    +----+------+
    1 row in set (0.00 sec)

    参考:

    http://topic.csdn.net/u/20090531/13/faf9fbb4-2a16-4da8-a54b-e1cd2cf55548.html

    http://blog.xhbin.com/archives/155

    http://farmeryxb.iteye.com/blog/332643


    展开全文
  • <div><p>I think it would be good to have a reminder of which archived mails are about to be deleted (just like the reminder of the subscribers who are about to be unsubscribed), so here it is.</p><p>...
  • I am trying to avoid deletion of more than 1 row at a time in MySQL by using a BEFORE DELETE trigger.The sample table and trigger are as below.Table test:DROP TABLE IF EXISTS `test`;CREATE TABLE `test...

    I am trying to avoid deletion of more than 1 row at a time in MySQL by using a BEFORE DELETE trigger.

    The sample table and trigger are as below.

    Table test:

    DROP TABLE IF EXISTS `test`;

    CREATE TABLE `test` (

    `id` int(11) NOT NULL AUTO_INCREMENT,

    `a` int(11) NOT NULL,

    `b` int(11) NOT NULL,

    PRIMARY KEY (`id`));

    INSERT INTO `test` (`id`, `a`, `b`)

    VALUES (1, 1, 2);

    INSERT INTO `test` (`id`, `a`, `b`)

    VALUES (2, 3, 4);

    Trigger:

    DELIMITER //

    DROP TRIGGER IF EXISTS prevent_multiple_deletion;

    CREATE TRIGGER prevent_multiple_deletion

    BEFORE DELETE ON test

    FOR EACH STATEMENT

    BEGIN

    IF(ROW_COUNT()>=2) THEN

    SIGNAL SQLSTATE '45000'

    SET MESSAGE_TEXT = 'Cannot delete more than one order per time!';

    END IF;

    END //

    DELIMITER ;

    This is still allowing multiple rows deletion. Even if I change the IF to >= 1, still allows the operation.

    I my idea is to avoid operations such as:

    DELETE FROM `test` WHERE `id`< 5;

    Can you help me? I know that the current version of MySQL doesn't allow FOR EACH STATEMENT triggers.

    Thank you!

    解决方案

    Firstly, getting some syntax error(s) out of our way, from your original attempt:

    Instead of FOR EACH STATEMENT, it should be FOR EACH ROW.

    Since you have already defined the Delimiter to //; you need to use // (instead of ;) in the DROP TRIGGER IF EXISTS .. statement.

    Row_Count() will have 0 value in a Before Delete Trigger, as no rows have been updated yet. So this approach will not work.

    Now, the trick here is to use Session-level Accessible (and Persistent) user-defined variables. We can define a variable, let's say @rows_being_deleted, and later check whether it is already defined or not.

    For Each Row runs the same set of statements for every row being deleted. So, we will just check whether the session variable already exists or not. If it does not, we can define it. So basically, for the first row (being deleted), it will get defined, which will persist as long as the session is there.

    Now if there are more rows to be deleted, Trigger would be running the same set of statements for the remaining rows. In the second row, the previously defined variable would be found now, and we can simply throw an exception now.

    Note that there is a chance that within the same session, multiple delete statements may get triggered. So before throwing exception, we need to set the @rows_being_deleted value back to null.

    Following will work:

    DELIMITER //

    DROP TRIGGER IF EXISTS prevent_multiple_deletion //

    CREATE TRIGGER prevent_multiple_deletion

    BEFORE DELETE ON `test`

    FOR EACH ROW

    BEGIN

    -- check if the variable is already defined or not

    IF( @rows_being_deleted IS NULL ) THEN

    SET @rows_being_deleted = 1; -- set its value

    ELSE -- it already exists and we are in next "row"

    -- just for testing to check the row count

    -- SET @rows_being_deleted = @rows_being_deleted + 1;

    -- We have to reset it to null, as within same session

    -- another delete statement may be triggered.

    SET @rows_being_deleted = NULL;

    -- throw exception

    SIGNAL SQLSTATE '45000'

    SET MESSAGE_TEXT = 'Cannot delete more than one order per time!';

    END IF;

    END //

    DELIMITER ;

    DELETE FROM `test` WHERE `id`< 5;

    Result:

    Query Error: Error: ER_SIGNAL_EXCEPTION: Cannot delete more than one

    order per time!

    Query #1

    DELETE FROM `test` WHERE `id` = 1;

    Deletion successfully happened. We can check the remaining rows using Select.

    Query #2

    SELECT * FROM `test`;

    | id | a | b |

    | --- | --- | --- |

    | 2 | 3 | 4 |

    展开全文
  • https://stackoverflow.com/questions/17730905/is-there-a-way-to-delete-all-the-data-from-a-topic-or-delete-the-topic-before-ev stop zookeeper & Kafka server, then go to ‘kafka-logs’ folder , ...

    工作中因为各种原因, 例如topic中消息堆积的太多,或者kafka所在磁盘空间满了等等,可能需要彻底清理一下kafka topic,那么如何彻底删除topic?
    方法一(配置delete.topic.enable=true)
      修改kafaka配置文件server.properties, 添加delete.topic.enable=true,重启kafka,之后通过kafka命令行就可以直接删除topic
      通过命令行删除topic:

        ./bin/kafka-topics.sh --delete --zookeeper {zookeeper server} --topic {topic name}

    方法二(没有配置delete.topic.enable=true)

    1、通过命令行删除topic:

    ./bin/kafka-topics.sh --delete --zookeeper {zookeeper server} --topic {topic name}

      因为kafaka配置文件中server.properties没有配置delete.topic.enable=true,此时的删除并不是真正的删除,只是把topic标记为:marked for deletion
      你可以通过命令:./bin/kafka-topics --zookeeper {zookeeper server} --list 来查看所有topic
    2、删除kafka存储目录(server.properties文件log.dirs配置,默认为"/tmp/kafka-logs")相关topic目录
    在这里插入图片描述

    3, 若想真正删除它,需要登录zookeeper客户端:

      命令:./bin/zkCli.sh

      找到topic所在的目录:ls /brokers/topics

       执行命令:rmr /brokers/topics/{topic name}即可,此时topic被彻底删除。

      另外被标记为marked for deletion的topic你可以在zookeeper客户端中通过命令获得:ls /admin/delete_topics/{topic name},如果你删除了此处的topic,那么marked for deletion 标记消失

    zookeeper 的config中也有有关topic的信息: ls /config/topics/{topic name}暂时不知道有什么用

    总结

    彻底删除topic:
    1、确保kafka的配置文件server.proeprties中设置delete.topic.enable=true,如果没有, 确保cluster的所有kafka配置文件设置该参数并重启,然后直接通过命令删除,如果命令删除不了,直接通过zookeeper命令行删除掉broker下的topic。

    2、删除kafka存储目录(server.properties文件log.dirs配置,默认为"/tmp/kafka-logs")相关topic目录

    参考文档:
    1, https://stackoverflow.com/questions/17730905/is-there-a-way-to-delete-all-the-data-from-a-topic-or-delete-the-topic-before-ev

    1. stop zookeeper & Kafka server,
    2. then go to ‘kafka-logs’ folder , there you will see list of kafka topic folders, delete folder with topic name
    3. go to ‘zookeeper-data’ folder , delete data inside that.
    4. start zookeeper & kafka server again.

    2, https://cwiki.apache.org/confluence/display/KAFKA/FAQ#FAQ-Isitpossibletodeleteatopic?
    Deleting a topic is supported since 0.8.2.x. You will need to enable topic deletion (setting delete.topic.enable to true) on all brokers first.

    3, https://cwiki.apache.org/confluence/display/KAFKA/KIP-162+-+Enable+topic+deletion+by+default

    4, https://github.com/darrenfu/bigdata/issues/6

    *** The only way to delete a topic permanently is as follows: ***

    stop the brokers
    sudo supervisorctl stop kafka-broker
    remove the directories on disk
    sudo rm -rf <kafka_data_dir>/<topic_name>*
    remove the topic from zookeeper:
    bin/zkCli.sh
    

    rmr /config/topics/<topic_name>
    rmr /brokers/topics/<topic_name>
    rmr /admin/delete_topics/<topic_name>

    verify the topic directory is deleted in zookeeper:
    

    ls

    start the brokers back up
    sudo supervisorctl start kafka-broker
    re-create the kafka topic if needed
    
    展开全文
  • Some RMAN commands return this error: RMAN-08591: WARNING: invalid archived log deletion policy RMAN may be deleting archivelog files from the primary database before they're transported/applied on ...
  • 最近在使用CEPH的过程中,删除pool的时候提示以下错误: $ ceph osd pool delete cephrbd cephrbd --yes...Error EPERM: pool deletion is disabled; you must first set the mon_allow_pool_delete config option...
  • Generally we do addition or deletion of components by going to CO02 transaction and then to component overview and do the operation. But what if we need to do it from program level. The below code ...
  • React before mutation阶段

    2022-04-10 20:51:57
    before mutation阶段 mutation阶段 layout阶段 主要都是在commitRootImpl函数内进行调用 commitRootImpl 这里只展示调用before mutation阶段的代码 if (firstEffect !== null) { // 保存之前的优先级,以同步...
  • Deletion

    2010-10-06 04:08:00
    5.5 Deletion Deletion in an AVL tree is remarkably similar to insertion. The steps that we go through are analogous: Search for the item to delete. Delete the item. Update balance...
  • XV6操作系统make报错Makefile:192: *** recipe commences before first target. Stop. 的解决方法   大家好,我叫亓官劼(qí guān jié ),在CSDN中记录学习的点滴历程,时光荏苒,未来可期,加油~博客地址为...
  • This blog basically explains how to generate a program for selective deletion Case: If you have an infoprovider, you want to perform a selective deletion with specified selections based on DSO va
  • <div><p>Make sure these boxes are checked before your pull request is ready to be reviewed and merged. Thanks! <p>sir...kindly review this PR . <ul><li>[X] all tests pass -- <code>rake test:all</code>...
  • "deletion of [ $line ]" ; then DEL[index]= " $line " ; index=$(( $index + 1 )); fi done # Print the confirmed changes. echo "All changes lists below: " echo "Additions:" for file...
  • destinations), before marking archive logs to be deleted. This configuration is specified using CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON ALL STANDBY . Allow optional archive log ...
  • model <a href="https://github.com/OCA/OCB/blob/12.0/odoo/addons/base/models/ir_cron.py#L52">restricts</a> deletion of the linked server action, it breaks at this point without the patch</p><p>该提问...
  • The insertion, and deletion operations, along with the tree rearrangement and recoloring are also performed in  O (log  n ) time. [ 1 ] Tracking the color of each node requires only 1 bit of...
  • (Linux ext2 filesystem why is there a deletion time?)ext2 inode有一个字段i_dtime,即删除时间。直到今天我还以为删除文件会删除inode。那么有谁知道这个领域的意义是什么?谢谢!the ext2 inode has a field i_...
  • 一、问题现象   由于前一段时间ORC delta文件合并失败,HDFS中... 因为我们把 dfs.namenode.startup.delay.block.deletion.sec (hdfs-site.xml) 参数设置为 3600,所以系统启动后一小时内不进行块删除。  
  • // needed to unlink next upon deletion boolean red; TreeNode(int hash, K key, V val, Node next) { super(hash, key, val, next); } } 以上源码中LinkedHashMapEntry就是LinkedHashMap中的每一个键值对对象,...
  • You must remove the fields before uninstalling. 然后到D:\PHPWeb\drupal-7.14\sites\all\modules目录下面将button_field目录删除掉 运行cron(可能需要多次),保存设置,来完成删除任务。 重新安装模块。...
  • 目录 概述 语法 资源类型 输出选项 实战 基本命令 create get edit delete 集群管理 ... --edit=false: Edit the API resource before creating -f, --filename=[]: Filename, directory, or URL to files to use...
  • // waitForContainersOnShutdownMillis : 6250 waitForContainersOnShutdownMillis = conf.getLong(YarnConfiguration.NM_SLEEP_DELAY_BEFORE_SIGKILL_MS, YarnConfiguration.DEFAULT_NM_SLEEP_DELAY_BEFORE_...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 10,236
精华内容 4,094
关键字:

deletion before