CODEWITHSUNDEEP
mysqlsqlcreate-table
Randall Helms
Randall Helms

Reputation: 859

Mysql: Adding a trigger to a Create Table statement

I am creating a database in MySql and I want to add some check constraints, however since MySQL doesn't support these, I see that I need to add them as triggers. What is not clear is where to do so in the piece of code.

Here's an example piece of code:

DROP SCHEMA IF EXISTS dwh_test;
CREATE SCHEMA dwh_test;
USE dwh_test;

--make Table 1
CREATE TABLE table_1 (
var1 VARCHAR(8) not null,
var2 VARCHAR(50) not null,
var3 VARCHAR(50) not null,
char1 CHAR(1) not null,
var4 VARCHAR(50) not null,
int1 INTEGER(7) not null,
var5 VARCHAR(50) not null,
char2 CHAR(2) not null,
int2 INTEGER(5) not null,
PRIMARY KEY (var1)
);

-- make Table 2

CREATE TABLE table_2 (
var6 VARCHAR(8) not null,
date1 DATE not null,
date2 DATE not null,
var7 VARCHAR(8) not null,   
var8 VARCHAR(8) not null,
date3 DATE,
var9 VARCHAR(10) not null,
float1 FLOAT(2) not null,
int3 INTEGER(7) not null,
char3 CHAR(5),
PRIMARY KEY (var6),
FOREIGN KEY (var7) REFERENCES table_1 (var1),
CONSTRAINT `ck_int3` CHECK (int3 > 0) 
CONSTRAINT `ck_var9` CHECK (var9 IN ("Pending","Denied","Approved"))
);

How do I write the two constraints in table_2 as triggers and where would they go in the overall statement?

For example, would this be a correct trigger statement for ck_int3?

CREATE TRIGGER ck_int3 BEFORE INSERT ON dwh_test
FOR EACH ROW 
BEGIN 
IF (int3 < 1)THEN 
SET int3=1; 
END IF; 
END

Upvotes: 0

Views: 1030

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269753

Close. Here is one way to write the trigger:

DELIMITER $$

CREATE TRIGGER ck_int3 BEFORE INSERT ON dwh_test
FOR EACH ROW 
BEGIN 
    SET new.int3 = GREATEST(1, new.int3)
END IF; 
END;$$
DELIMITER ;

Upvotes: 2

Related Questions