templates.velocity.ro_notify.mysql.install_ro_notify_trigger.sql.vm Maven / Gradle / Ivy

Go to download
Show more of this group Show more artifacts with this name
Show all versions of sql2java-observer Show documentation
table listener base row observer UDF
There is a newer version: 5.2.0
#parse( "header.include.vm")

#set ( $trigger_prefix = $schematable.replace(".","_") )
# 创建${schematable}表触发器 AFTER_INSERT
DROP TRIGGER IF EXISTS ${trigger_prefix}_after_insert_trigger ;
#if($delimiter)
DELIMITER @@
#end
CREATE TRIGGER ${trigger_prefix}_after_insert_trigger AFTER INSERT ON ${schematable}
FOR EACH ROW
BEGIN
  # 调用UDF ro_notify
  # 将所记录的所有字段以JSON格式式传递给UDF
  SELECT ro_notify(
  # 表名: 格式 $schema.$tablename
  '${schematable}' as tablename, 
  #=============================================================================#
  # 触发器类型:                                                                 #
  # AFTER_UPDATE                                                                #
  # AFTER_INSERT                                                                #
  # AFTER_DELETE                                                                #
  # BEFORE_UPDATE                                                               #
  # BEFORE_INSERT                                                               #
  # BEFORE_DELETE                                                               #
  #=============================================================================#
  'AFTER_INSERT' as trigger_type, 
  #=============================================================================#
  # 字段数据存储的方式:                                                         #
  # JSON 将所有字段保存在一个JSON字符串传递给动态库                             #
  #=============================================================================#
  'JSON' as store_type,
  JSON_OBJECT(#join($columnNames "'$e',NEW.$e" ',')) as NEW
  ) 
  INTO @x;
END; #if($delimiter)@@
DELIMITER ;#end 

# 创建${schematable}表触发器 BEFORE_INSERT
DROP TRIGGER IF EXISTS ${trigger_prefix}_before_insert_trigger ;
#if($delimiter)
DELIMITER @@
#end
CREATE TRIGGER ${trigger_prefix}_before_insert_trigger BEFORE INSERT ON ${schematable}
FOR EACH ROW
BEGIN
  SELECT ro_notify(
  # 表名: 格式 $schema.$teablename
  '${schematable}' as tablename, 
  'BEFORE_INSERT' as trigger_type, 
    'JSON' as store_type,
  JSON_OBJECT(#join($columnNames "'$e',NEW.$e" ',')) as NEW
  ) 
  INTO @x;
END; #if($delimiter)@@
DELIMITER ;#end

# 创建${schematable}表触发器 AFTER_UPDATE
DROP TRIGGER IF EXISTS ${trigger_prefix}_after_update_trigger ;

CREATE TRIGGER ${trigger_prefix}_after_update_trigger AFTER UPDATE ON ${schematable}
#if($delimiter)
DELIMITER @@
#end
FOR EACH ROW
BEGIN
  SELECT ro_notify(
  '${schematable}' as tablename, 
  'AFTER_UPDATE' as trigger_type, 
    'JSON' as store_type,
  JSON_OBJECT(#join($columnNames "'$e',NEW.$e" ',')) as NEW,
  JSON_OBJECT(#join($columnNames "'$e',OLD.$e" ',')) as OLD
  ) 
  INTO @x;
END; #if($delimiter)@@
DELIMITER ;#end

# 创建${schematable}表触发器 BEFORE_UPDATE
DROP TRIGGER IF EXISTS ${trigger_prefix}_before_update_trigger ;

CREATE TRIGGER ${trigger_prefix}_before_update_trigger BEFORE UPDATE ON ${schematable}
#if($delimiter)
DELIMITER @@
#end
FOR EACH ROW
BEGIN
  SELECT ro_notify(
  '${schematable}' as tablename, 
  'BEFORE_UPDATE' as trigger_type, 
    'JSON' as store_type,
  JSON_OBJECT(#join($columnNames "'$e',NEW.$e" ',')) as NEW,
  JSON_OBJECT(#join($columnNames "'$e',OLD.$e" ',')) as OLD
  ) 
  INTO @x;
END; #if($delimiter)@@
DELIMITER ;#end

# 创建${schematable}表触发器 AFTER_DELETE
DROP TRIGGER IF EXISTS ${trigger_prefix}_after_delete_trigger ;

CREATE TRIGGER ${trigger_prefix}_after_delete_trigger AFTER DELETE ON ${schematable}
#if($delimiter)
DELIMITER @@
#end
FOR EACH ROW
BEGIN
  SELECT ro_notify(
  '${schematable}' as tablename, 
  'AFTER_DELETE' as trigger_type, 
    'JSON' as store_type,
  JSON_OBJECT(#join($columnNames "'$e',OLD.$e" ',')) as OLD
  ) 
  INTO @x;
END; #if($delimiter)@@
DELIMITER ;#end

# 创建${schematable}表触发器 BEFORE_DELETE
DROP TRIGGER IF EXISTS ${trigger_prefix}_before_delete_trigger ;

CREATE TRIGGER ${trigger_prefix}_before_delete_trigger BEFORE DELETE ON ${schematable}
#if($delimiter)
DELIMITER @@
#end
FOR EACH ROW
BEGIN
  SELECT ro_notify(
  '${schematable}' as tablename, 
  'BEFORE_DELETE' as trigger_type, 
    'JSON' as store_type,
  JSON_OBJECT(#join($columnNames "'$e',OLD.$e" ',')) as OLD
  ) 
  INTO @x;
END; #if($delimiter)@@
DELIMITER ;#end