Module 1 - "Roles and Users" table design..continued

In this blog, i will write down the queries to create database and tables. We are using postgres.

Refer my previous blog to understand the table design:
http://productdesignsagain.blogspot.com/2015/09/module-1-roles-and-users-database-design.html

Create database "PMS" :

CREATE DATABASE "PMS"
  WITH OWNER = "PMS"
       ENCODING = 'UTF8'
       TABLESPACE = pg_default
       LC_COLLATE = 'en_US'
       LC_CTYPE = 'en_US'
       CONNECTION LIMIT = -1;

COMMENT ON DATABASE "PMS"
  IS 'Database for Independent Pharma Management System application developed by Ruach's Tsidkenu LTD';

Next, create table "roles"
-------------------------------------
CREATE TABLE roles
(
  role_id serial NOT NULL,
  read character varying(1) NOT NULL,
  insert character varying(1) NOT NULL,
  modify character varying(1) NOT NULL,
  CONSTRAINT roles_pkey PRIMARY KEY (role_id),
  CONSTRAINT roles_insert_check CHECK (insert::text = ANY (ARRAY['Y'::character varying, 'N'::character varying]::text[])),
  CONSTRAINT roles_modify_check CHECK (modify::text = ANY (ARRAY['Y'::character varying, 'N'::character varying]::text[])),
  CONSTRAINT roles_read_check CHECK (read::text = ANY (ARRAY['Y'::character varying, 'N'::character varying]::text[]))

******Add CHECK constraint to limit the entry to 'Y' and 'N' values only.********

)
WITH (
  OIDS=FALSE
);

INSERT INTO roles(READ,INSERT,MODIFY) VALUES('Y','Y','Y');
INSERT INTO roles(READ,INSERT,MODIFY) VALUES('Y','Y','N');

SELECT * FROM roles

Create table "users"
--------------------------
CREATE TABLE users (
user_id SERIAL,
password varchar(10) NOT NULL,
email_id varchar(30) NOT NULL,
create_date TIMESTAMP ,
-- /*not including this column yet */modify_date TIMESTAMP ,
PRIMARY KEY (user_id)
);

****Write a procedure to auto update columns 'create_date' and 'modify_date' after insert or update of a column in table 'users'. NOTE: column 'create_date' is modified only the first time it is created.******

Procedure:

CREATE OR REPLACE FUNCTION after_insert_update()
RETURNS TRIGGER
AS $BODY$ BEGIN
        UPDATE users
        SET create_date = CASE -- if coulumn 'create_date' is empty then update it's value
WHEN create_date IS NULL
THEN current_timestamp

END,
  modify_date = current_timestamp
--****** column 'modify_date' is updated each time user record is updated*****
WHERE user_id = new.user_id;
RETURN NEW;
 END;
$BODY$ LANGUAGE plpgsql;

Trigger: Create 2 triggers:
1. To run after insertion.
2. To run after updation.

Both triggers use common procedure 'after_insert_update()':

CREATE TRIGGER insert_trigger AFTER INSERT ON users
FOR EACH ROW EXECUTE PROCEDURE after_insert_update();

-- not including this trigger yet,, /*CREATE TRIGGER update_trigger AFTER UPDATE ON users
--FOR EACH ROW EXECUTE PROCEDURE after_insert_update();
---yet,, keeping only 1 trigger */

Create table "modules"
-----------------------------------
CREATE TABLE modules (
module_id SERIAL,
module_name varchar(30) UNIQUE,
user_id INTEGER NOT NULL references users(user_id),
role_id INTEGER NOT NULL references roles(role_id),
PRIMARY KEY (module_id)
);