diff --git a/util/src/main/resources/com/ning/billing/util/ddl.sql b/util/src/main/resources/com/ning/billing/util/ddl.sql
index 389efa7..4ae95d7 100644
--- a/util/src/main/resources/com/ning/billing/util/ddl.sql
+++ b/util/src/main/resources/com/ning/billing/util/ddl.sql
@@ -17,22 +17,27 @@ CREATE TABLE custom_field_history (
id char(36) NOT NULL,
object_id char(36) NOT NULL,
object_type varchar(30) NOT NULL,
- field_name varchar(30) NOT NULL,
- field_value varchar(255) NOT NULL,
- change_type char(6) NOT NULL,
- date datetime NOT NULL
+ field_name varchar(30),
+ field_value varchar(255),
+ date datetime NOT NULL,
+ change_type char(6) NOT NULL
) ENGINE=innodb;
CREATE INDEX custom_field_history_object_id_object_type ON custom_fields(object_id, object_type);
CREATE TRIGGER store_custom_field_history_on_insert AFTER INSERT ON custom_fields
FOR EACH ROW
- INSERT INTO custom_field_history (id, object_id, object_type, field_name, field_value, change_type, date)
- VALUES (NEW.id, NEW.object_id, NEW.object_type, NEW.field_name, NEW.field_value, 'CREATE', NEW.created_date);
+ INSERT INTO custom_field_history (id, object_id, object_type, field_name, field_value, date, change_type)
+ VALUES (NEW.id, NEW.object_id, NEW.object_type, NEW.field_name, NEW.field_value, NOW(), 'CREATE');
CREATE TRIGGER store_custom_field_history_on_update AFTER UPDATE ON custom_fields
FOR EACH ROW
- INSERT INTO custom_field_history (id, object_id, object_type, field_name, field_value, change_type, date)
- VALUES (NEW.id, NEW.object_id, NEW.object_type, NEW.field_name, NEW.field_value, 'UPDATE', NEW.updated_date);
+ INSERT INTO custom_field_history (id, object_id, object_type, field_name, field_value, date, change_type)
+ VALUES (NEW.id, NEW.object_id, NEW.object_type, NEW.field_name, NEW.field_value, NOW(), 'UPDATE');
+
+CREATE TRIGGER store_custom_field_history_on_delete BEFORE DELETE ON custom_fields
+ FOR EACH ROW
+ INSERT INTO custom_field_history (id, object_id, object_type, field_name, field_value, date, change_type)
+ VALUES (OLD.id, OLD.object_id, OLD.object_type, NULL, NULL, NOW(), 'DELETE');
DROP TABLE IF EXISTS tag_descriptions;
DROP TABLE IF EXISTS tag_definitions;
@@ -47,6 +52,33 @@ CREATE TABLE tag_definitions (
) ENGINE=innodb;
CREATE UNIQUE INDEX tag_definitions_name ON tag_definitions(name);
+DROP TABLE IF EXISTS tag_definition_history;
+CREATE TABLE tag_definition_history (
+ id char(36) NOT NULL,
+ name varchar(20) NOT NULL,
+ created_by varchar(50),
+ description varchar(200),
+ date datetime NOT NULL,
+ change_type char(6) NOT NULL
+) ENGINE=innodb;
+CREATE INDEX tag_definition_history_id ON tag_definition_history(id);
+CREATE INDEX tag_definition_history_name ON tag_definition_history(name);
+
+CREATE TRIGGER tag_definition_history_after_insert AFTER INSERT ON tag_definition_history
+ FOR EACH ROW
+ INSERT INTO tag_definition_history (id, name, created_by, description, date, change_type)
+ VALUES (NEW.id, NEW.name, NEW.created_by, NEW.description, NOW(), 'CREATE');
+
+CREATE TRIGGER tag_definition_history_after_update AFTER UPDATE ON tag_definition_history
+ FOR EACH ROW
+ INSERT INTO tag_definition_history (id, name, created_by, description, date, change_type)
+ VALUES (NEW.id, NEW.name, NEW.created_by, NEW.description, NOW(), 'UPDATE');
+
+CREATE TRIGGER tag_definition_history_before_delete BEFORE DELETE ON tag_definition_history
+ FOR EACH ROW
+ INSERT INTO tag_definition_history (id, name, created_by, description, date, change_type)
+ VALUES (OLD.id, OLD.name, NULL, NULL, NOW(), 'DELETE');
+
DROP TABLE IF EXISTS tags;
CREATE TABLE tags (
id char(36) NOT NULL,
@@ -60,6 +92,32 @@ CREATE TABLE tags (
CREATE INDEX tags_by_object ON tags(object_id);
CREATE UNIQUE INDEX tags_unique ON tags(tag_definition_name, object_id);
+DROP TABLE IF EXISTS tag_history;
+CREATE TABLE tag_history (
+ id char(36) NULL,
+ tag_definition_name varchar(20) NOT NULL,
+ object_id char(36) NOT NULL,
+ object_type varchar(30) NOT NULL,
+ date datetime NOT NULL,
+ change_type char(6) NOT NULL
+) ENGINE = innodb;
+CREATE INDEX tag_history_by_object ON tags(object_id);
+
+CREATE TRIGGER tag_history_after_insert AFTER INSERT ON tag_history
+ FOR EACH ROW
+ INSERT INTO tag_history (id, tag_definition_name, object_id, object_type, date, change_type)
+ VALUES (NEW.id, NEW.tag_definition_name, NEW.object_id, NEW.object_type, NOW(), 'CREATE');
+
+CREATE TRIGGER tag_history_after_update AFTER UPDATE ON tag_history
+ FOR EACH ROW
+ INSERT INTO tag_history (id, tag_definition_name, object_id, object_type, date, change_type)
+ VALUES (NEW.id, NEW.tag_definition_name, NEW.object_id, NEW.object_type, NOW(), 'UPDATE');
+
+CREATE TRIGGER tag_history_before_delete BEFORE DELETE ON tag_history
+ FOR EACH ROW
+ INSERT INTO tag_history (id, tag_definition_name, object_id, object_type, date, change_type)
+ VALUES (OLD.id, OLD.tag_definition_name, OLD.object_id, OLD.object_type, NOW(), 'DELETE');
+
DROP TABLE IF EXISTS notifications;
CREATE TABLE notifications (
id int(11) unsigned NOT NULL AUTO_INCREMENT,
@@ -85,4 +143,4 @@ CREATE TABLE claimed_notifications (
claimed_dt datetime NOT NULL,
notification_id char(36) NOT NULL,
PRIMARY KEY(id)
-) ENGINE=innodb;
+) ENGINE=innodb;
\ No newline at end of file
