更改表名和触发器流程和命令

1、查询旧表的触发器名字从而进行删除旧触发器。

查看当前表的触发器名称 event_object_table = '表名'

-- 查看当前表的触发器名称 event_object_table = '原表名'
SELECT trigger_name, event_object_table
FROM information_schema.triggers
WHERE event_object_table = 'b_project_data';

-- 查看当前表的触发器名称 event_object_table = '新表名'
SELECT trigger_name, event_object_table
FROM information_schema.triggers
WHERE event_object_table = 'bb_project_data';

2、删除旧触发器。

示例：

DROP TRIGGER 触发器名字 ON 设置触发器的表名;

-- 删除旧表触发器 trg_after_delete_content 等是通过查询出触发器的名字，从而进行删除
DROP TRIGGER trg_after_delete_content ON bb_project_data;

DROP TRIGGER trg_after_insert_content ON bb_project_data;

DROP TRIGGER trg_after_update_content ON bb_project_data;

3、修改表名。

示例：

ALTER TABLE OldTableName RENAME TO NewTableName ;

-- 修改表名
ALTER TABLE a_project RENAME TO aa_project;
​
ALTER TABLE b_project_data RENAME TO bb_project_data;
​
ALTER TABLE c_project_data_log RENAME TO cc_project_data_log;

4、更新触发器。

-- 更新触发器语句

CREATE OR REPLACE FUNCTION trg_after_update_content_func() RETURNS TRIGGER AS $$
BEGIN
    INSERT INTO cc_project_data_log (original_id, project_id, original_content, operate_type, update_time)
    VALUES (OLD.id, OLD.project_id, OLD.content, 'UPDATE', NOW());
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;
​
​
CREATE TRIGGER trg_after_update_content
AFTER UPDATE ON bb_project_data
FOR EACH ROW
EXECUTE PROCEDURE trg_after_update_content_func();
​


-- 删除触发器

CREATE OR REPLACE FUNCTION trg_after_delete_content_func() RETURNS TRIGGER AS $$
BEGIN
    INSERT INTO cc_project_data_log (original_id, project_id, original_content, operate_type, update_time)
    VALUES (OLD.id, OLD.project_id, OLD.content, 'DELETE', NOW());
    RETURN OLD;
END;
$$ LANGUAGE plpgsql;
​
​
CREATE TRIGGER trg_after_delete_content
AFTER DELETE ON bb_project_data
FOR EACH ROW EXECUTE PROCEDURE trg_after_delete_content_func();
​


-- 创建插入数据的触发器


CREATE OR REPLACE FUNCTION trg_after_insert_content_func() RETURNS TRIGGER AS $$
BEGIN
    INSERT INTO cc_project_data_log (original_id, project_id,original_content,operate_type,update_time)
    VALUES (NEW.id,NEW.project_id,NEW.content, 'INSERT', NOW());
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;
​
​
CREATE TRIGGER trg_after_insert_content AFTER INSERT ON bb_project_data 
FOR EACH ROW EXECUTE PROCEDURE trg_after_insert_content_func();



删除表所有数据语句：

delete from bb_project_data_log;（错误）

DELETE from bb_project_data_log;（正确）