Elopade Database design follows a very crisp structure.
Each table created has an automated procedure linked to it which gives it a "Table ID". You cannot create a table ID unless that table ID is registered to a module. This gives flexibility to have different levels of admin and super users.
1st - we create a table to store user detail. This table initially has only firstname, lastname, uid , pswd, email, modifiedby and createby to store. But after we create 2 more tables, we alter this table to add new columns - table id and roleID. I am deliberately creating this table initially without these columns as at this point table from which tableid needs to be referenced is not yet created. So follow the steps, which may look messy at first but works out just fine.
Just for clarity purpose i have added a naming convention to tables and procedures(which i personally detest to do ) but it is vital, somehow.
so here we go:
create table to hold users detail (note: at this point i have not added createdby, modifiedby or tableid, which i do after few more queries):
create table admin_m_users
(
firstname varchar(50),
lastname varchar(50),
uid varchar(30) primary key,
pwd varchar(200),
emailID varchar(30),
createdDate timestamp,
modifiedDate timestamp
)
create a procedure to auto update insert date. Modified date is saved from the backend program and not with the procedure. Honestly, my stack memory went low so i could not create a trigger for after update. I had increased stack memory to 1 GB in pg config file,, but it wasnt working. So my workaround - I update modified date from application and then save it as a value in DB:
NOTE: Our encryption happens in procedure:
CREATE OR REPLACE FUNCTION admin_after_insert_admin_m_users()
RETURNS TRIGGER
AS $BODY$ BEGIN
IF new.createby IS NULL
THEN
UPDATE admin_m_users
SET createdDate = current_timestamp, createby = new.uid, modifiedby = new.uid,
pwd = crypt(NEW.pwd, gen_salt('bf'))
WHERE uid = new.uid;
ELSE
UPDATE admin_m_users
SET createdDate = current_timestamp,
pwd = crypt(NEW.pwd, gen_salt('bf'))
WHERE uid = new.uid;
END IF;
RETURN NEW;
END;
$BODY$ LANGUAGE plpgsql;
Trigger to execute above procedure:
CREATE TRIGGER admin_trigger_Afterinsert_admin_m_users AFTER INSERT ON admin_m_users
FOR EACH ROW EXECUTE PROCEDURE admin_after_insert_admin_m_users();
Now, lets just insert a value in admin_m_users:
insert into admin_m_users values
('U01', 'PswdNew@007', 'khushboo@elopade.com');
Now you can test and see if created date is auto filled.
Next,
we create a table to link modules with tables. Each table ever created will be registered here. admin_m_users too will get registered here, after which i alter table admin_m_users to add table ID:
create table admin_m_moduleTableLink
(
moduleID varchar(30) NOT NULL,
tableID varchar(30) NOT NULL,
ModuleWithtableDescription varchar(60) UNIQUE,
createBy varchar(30)references admin_m_users(uid),
modifiedBy varchar(30)references admin_m_users(uid),
createdDate timestamp,
modifiedDate timestamp,
CONSTRAINT compositePair PRIMARY KEY (moduleID,tableID)
)
Procedure:
CREATE OR REPLACE FUNCTION admin_after_insert_admin_m_moduleTableLink()
RETURNS TRIGGER
AS $BODY$ BEGIN
UPDATE admin_m_moduleTableLink
SET ModuleWithtableDescription = concat(moduleID, tableID), createdDate = current_timestamp
WHERE moduleID = new.moduleID and tableID = new.tableID;
RETURN NEW;
END;
$BODY$ LANGUAGE plpgsql;
Trigger:
CREATE TRIGGER admin_trigger_Afterinsert_admin_m_moduleTableLink AFTER INSERT ON admin_m_moduleTableLink
FOR EACH ROW EXECUTE PROCEDURE admin_after_insert_admin_m_moduleTableLink();
Now register all the tables which will be or can be created:
insert into admin_m_moduleTableLink
values
(
'UserProfile', 'admin_m_encrypt'
);
insert into admin_m_moduleTableLink
values
(
'UserProfile', 'admin_m_users'
);
insert into admin_m_moduleTableLink
values
(
'UserProfile', 'admin_m_roles'
);
insert into admin_m_moduleTableLink
values
(
'UserProfile', 'admin_m_module_usrRelation'
);
insert into admin_m_moduleTableLink
values
(
'Goods', 'admin_m_itemMaster'
);
insert into admin_m_moduleTableLink
values
(
'Goods', 'admin_m_itemBOM'
);
insert into admin_m_moduleTableLink
values
(
'Partner', 'admin_m_businessPartner'
);
Now 'modulewithtabledescription' will be used as tableID in all respective tables:
Now create table for roles, permission values are restricted to Y(yes) and N(No).
Different combinations can define different level of privileges. Admin will have all Y.
create table admin_m_roles
(
roleID varchar(30) primary key,
mread varchar(1),
tableID varchar(40) references admin_m_moduleTableLink(ModuleWithtableDescription),
minsert varchar(1),
mupdate varchar(1),
mdelete varchar(1),
createBy varchar(30)references admin_m_users(uid),
modifiedBy varchar(30)references admin_m_users(uid),
createdDate timestamp,
modifiedDate timestamp,
CHECK ((mread = 'Y' or mread = 'N' ) and (minsert = 'Y' or minsert = 'N') and (mupdate = 'Y' or mupdate = 'N')
and (mdelete = 'Y' or mdelete = 'N'))
)
Procedure to audo fill table id and create date:
CREATE OR REPLACE FUNCTION admin_after_insert_admin_m_roles()
RETURNS TRIGGER
AS $BODY$ BEGIN
UPDATE admin_m_roles
SET tableID = 'UserProfileadmin_m_roles', createdDate = current_timestamp
WHERE roleID = new.roleID;
RETURN NEW;
END;
$BODY$ LANGUAGE plpgsql;
trigger for above procedure:
CREATE TRIGGER admin_trigger_Afterinsert_admin_m_roles AFTER INSERT ON admin_m_roles
FOR EACH ROW EXECUTE PROCEDURE admin_after_insert_admin_m_roles();
Now delete procedure admin_after_insert_admin_m_users. (Keep following what i say here):
Delete trigger :admin_trigger_Afterinsert_admin_m_users
ALTER TABLE admin_m_users
ADD tableID varchar(40) references admin_m_moduleTableLink(ModuleWithtableDescription),
ADD createBy varchar(30)references admin_m_users(uid),
ADD modifiedBy varchar(30)references admin_m_users(uid),
ADD roleID varchar(30) references admin_m_roles(roleID);
Note: Now admin_m_users have role added to it along with others above.
Now recreate procedure and trigger for admin_m_users:
CREATE OR REPLACE FUNCTION admin_after_insert_admin_m_users()
RETURNS TRIGGER
AS $BODY$ BEGIN
IF new.createby IS NULL
THEN
UPDATE admin_m_users
SET createdDate = current_timestamp, tableID = 'UserProfileadmin_m_users', createby = new.uid, modifiedby = new.uid,
pwd = crypt(NEW.pwd, gen_salt('bf'))
WHERE uid = new.uid;
ELSE
UPDATE admin_m_users
SET createdDate = current_timestamp, tableID = 'UserProfileadmin_m_users',
pwd = crypt(NEW.pwd, gen_salt('bf'))
WHERE uid = new.uid;
END IF;
RETURN NEW;
END;
$BODY$ LANGUAGE plpgsql;
Trigger:
CREATE TRIGGER admin_trigger_Afterinsert_admin_m_users AFTER INSERT ON admin_m_users
FOR EACH ROW EXECUTE PROCEDURE admin_after_insert_admin_m_users();
Now link role with tables:
create table admin_m_module_usrRelation
(
tableID2 varchar(40) references admin_m_moduleTableLink(ModuleWithtableDescription),
tableID varchar(40) references admin_m_moduleTableLink(ModuleWithtableDescription), -- procedure
roleID varchar(30)references admin_m_roles(roleID),
createBy varchar(30)references admin_m_users(uid),
modifiedBy varchar(30)references admin_m_users(uid),
createdDate timestamp,
modifiedDate timestamp,
CONSTRAINT compositePair1 PRIMARY KEY (tableID2,roleID)
)
Usual procedure and trigger:
CREATE OR REPLACE FUNCTION admin_after_insert_admin_m_module_usrRelation()
RETURNS TRIGGER
AS $BODY$ BEGIN
UPDATE admin_m_module_usrRelation
SET tableID = 'UserProfileadmin_m_module_usrRelation', createdDate = current_timestamp
WHERE roleID = new.roleID and tableID2 = new.tableID2;
RETURN NEW;
END;
$BODY$ LANGUAGE plpgsql;
CREATE TRIGGER admin_trigger_Afterinsert_admin_m_module_usrRelation
AFTER INSERT ON admin_m_module_usrRelation
FOR EACH ROW
EXECUTE PROCEDURE admin_after_insert_admin_m_module_usrRelation();
Create Item Master, Restrict Item type to P(Parent), C(Child), PC(ParentAndChild):
create table admin_m_itemMaster
(
tableID varchar(40) references admin_m_moduleTableLink(ModuleWithtableDescription),
itemID varchar(30) primary key,
itemName varchar(40),
itemType varchar(2),
createBy varchar(30)references admin_m_users(uid),
modifiedBy varchar(30)references admin_m_users(uid),
createdDate timestamp,
modifiedDate timestamp,
totalCost float,
CHECK (itemType = 'P' or itemType = 'C' or itemType = 'PC')
)
Procedure to autolink it to registered table ID:
CREATE OR REPLACE FUNCTION admin_after_insert_admin_m_itemMaster()
RETURNS TRIGGER
AS $BODY$ BEGIN
UPDATE admin_m_itemMaster
SET tableID = 'Goodsadmin_m_itemMaster', createdDate = current_timestamp
WHERE itemID = new.itemID;
RETURN NEW;
END;
$BODY$ LANGUAGE plpgsql;
Trigger:
CREATE TRIGGER admin_trigger_Afterinsert_admin_m_itemMaster
AFTER INSERT ON admin_m_itemMaster
FOR EACH ROW
EXECUTE PROCEDURE admin_after_insert_admin_m_itemMaster();
Create Business Partener:
create table admin_m_businessPartner
(
tableID varchar(40) references admin_m_moduleTableLink(ModuleWithtableDescription),
BP_ID varchar(30) primary key,
BP_Type varchar(2),
BP_Name varchar(30),
createBy varchar(30)references admin_m_users(uid),
modifiedBy varchar(30)references admin_m_users(uid),
createdDate timestamp,
modifiedDate timestamp,
CHECK (BP_Type = 'C' or BP_Type = 'V' or BP_Type = 'CV')
)
CREATE OR REPLACE FUNCTION admin_after_insert_admin_m_businessPartner()
RETURNS TRIGGER
AS $BODY$ BEGIN
UPDATE admin_m_businessPartner
SET tableID = 'Partneradmin_m_businessPartner', createdDate = current_timestamp
WHERE BP_ID = new.BP_ID;
RETURN NEW;
END;
$BODY$ LANGUAGE plpgsql;
CREATE TRIGGER admin_trigger_Afterinsert_admin_m_businessPartner
AFTER INSERT ON admin_m_businessPartner
FOR EACH ROW
EXECUTE PROCEDURE admin_after_insert_admin_m_businessPartner();
Create a BOM table. This table links parent and child items and with relevant metrics. I have written the procedure in a such way that a parent Item should be registered as type 'P' in admin_m_item table:
create table admin_m_itemBOM
(
itemID varchar(30) NOT NULL,
parentID varchar(30) NOT NULL,
quantity int,
quantityUnit varchar(20),
price float,
vendorID varchar(30),
tableID varchar(40) references admin_m_moduleTableLink(ModuleWithtableDescription),
createBy varchar(30)references admin_m_users(uid),
modifiedBy varchar(30)references admin_m_users(uid),
createdDate timestamp,
modifiedDate timestamp,
CONSTRAINT itemID PRIMARY KEY (itemID,parentID),
CONSTRAINT itemIDF FOREIGN KEY (itemID)
REFERENCES admin_m_itemMaster (itemID),
CONSTRAINT parentIDF FOREIGN KEY (parentID)
REFERENCES admin_m_itemMaster (itemID),
CONSTRAINT vendorID FOREIGN KEY (vendorID)
REFERENCES admin_m_businessPartner (BP_ID)
)
Procedure:
CREATE OR REPLACE FUNCTION admin_after_insert_admin_m_itemBOM()
RETURNS TRIGGER
AS $BODY$ BEGIN
UPDATE admin_m_itemBOM
SET tableID = 'Goodsadmin_m_itemBOM',createdDate = current_timestamp
WHERE (itemID = new.itemID and parentID = new.parentID) and parentID IN (select itemID from admin_m_itemMaster where itemType = 'P');
delete from admin_m_itemBOM where tableID IS NULL;
RETURN NEW;
END;
$BODY$ LANGUAGE plpgsql;
Note: If parent ID is not among registered parent item, i delete the row in the above procedure.
Trigger to run above procedure:
CREATE TRIGGER admin_trigger_Afterinsert_admin_m_itemBOM
AFTER INSERT ON admin_m_itemBOM
FOR EACH ROW
EXECUTE PROCEDURE admin_after_insert_admin_m_itemBOM();
Each table created has an automated procedure linked to it which gives it a "Table ID". You cannot create a table ID unless that table ID is registered to a module. This gives flexibility to have different levels of admin and super users.
1st - we create a table to store user detail. This table initially has only firstname, lastname, uid , pswd, email, modifiedby and createby to store. But after we create 2 more tables, we alter this table to add new columns - table id and roleID. I am deliberately creating this table initially without these columns as at this point table from which tableid needs to be referenced is not yet created. So follow the steps, which may look messy at first but works out just fine.
Just for clarity purpose i have added a naming convention to tables and procedures(which i personally detest to do ) but it is vital, somehow.
so here we go:
create table to hold users detail (note: at this point i have not added createdby, modifiedby or tableid, which i do after few more queries):
create table admin_m_users
(
firstname varchar(50),
lastname varchar(50),
uid varchar(30) primary key,
pwd varchar(200),
emailID varchar(30),
createdDate timestamp,
modifiedDate timestamp
)
create a procedure to auto update insert date. Modified date is saved from the backend program and not with the procedure. Honestly, my stack memory went low so i could not create a trigger for after update. I had increased stack memory to 1 GB in pg config file,, but it wasnt working. So my workaround - I update modified date from application and then save it as a value in DB:
NOTE: Our encryption happens in procedure:
CREATE OR REPLACE FUNCTION admin_after_insert_admin_m_users()
RETURNS TRIGGER
AS $BODY$ BEGIN
IF new.createby IS NULL
THEN
UPDATE admin_m_users
SET createdDate = current_timestamp, createby = new.uid, modifiedby = new.uid,
pwd = crypt(NEW.pwd, gen_salt('bf'))
WHERE uid = new.uid;
ELSE
UPDATE admin_m_users
SET createdDate = current_timestamp,
pwd = crypt(NEW.pwd, gen_salt('bf'))
WHERE uid = new.uid;
END IF;
RETURN NEW;
END;
$BODY$ LANGUAGE plpgsql;
Trigger to execute above procedure:
CREATE TRIGGER admin_trigger_Afterinsert_admin_m_users AFTER INSERT ON admin_m_users
FOR EACH ROW EXECUTE PROCEDURE admin_after_insert_admin_m_users();
Now, lets just insert a value in admin_m_users:
insert into admin_m_users values
('U01', 'PswdNew@007', 'khushboo@elopade.com');
Now you can test and see if created date is auto filled.
Next,
we create a table to link modules with tables. Each table ever created will be registered here. admin_m_users too will get registered here, after which i alter table admin_m_users to add table ID:
create table admin_m_moduleTableLink
(
moduleID varchar(30) NOT NULL,
tableID varchar(30) NOT NULL,
ModuleWithtableDescription varchar(60) UNIQUE,
createBy varchar(30)references admin_m_users(uid),
modifiedBy varchar(30)references admin_m_users(uid),
createdDate timestamp,
modifiedDate timestamp,
CONSTRAINT compositePair PRIMARY KEY (moduleID,tableID)
)
Procedure:
CREATE OR REPLACE FUNCTION admin_after_insert_admin_m_moduleTableLink()
RETURNS TRIGGER
AS $BODY$ BEGIN
UPDATE admin_m_moduleTableLink
SET ModuleWithtableDescription = concat(moduleID, tableID), createdDate = current_timestamp
WHERE moduleID = new.moduleID and tableID = new.tableID;
RETURN NEW;
END;
$BODY$ LANGUAGE plpgsql;
Trigger:
CREATE TRIGGER admin_trigger_Afterinsert_admin_m_moduleTableLink AFTER INSERT ON admin_m_moduleTableLink
FOR EACH ROW EXECUTE PROCEDURE admin_after_insert_admin_m_moduleTableLink();
Now register all the tables which will be or can be created:
insert into admin_m_moduleTableLink
values
(
'UserProfile', 'admin_m_encrypt'
);
insert into admin_m_moduleTableLink
values
(
'UserProfile', 'admin_m_users'
);
insert into admin_m_moduleTableLink
values
(
'UserProfile', 'admin_m_roles'
);
insert into admin_m_moduleTableLink
values
(
'UserProfile', 'admin_m_module_usrRelation'
);
insert into admin_m_moduleTableLink
values
(
'Goods', 'admin_m_itemMaster'
);
insert into admin_m_moduleTableLink
values
(
'Goods', 'admin_m_itemBOM'
);
insert into admin_m_moduleTableLink
values
(
'Partner', 'admin_m_businessPartner'
);
Now 'modulewithtabledescription' will be used as tableID in all respective tables:
Now create table for roles, permission values are restricted to Y(yes) and N(No).
Different combinations can define different level of privileges. Admin will have all Y.
create table admin_m_roles
(
roleID varchar(30) primary key,
mread varchar(1),
tableID varchar(40) references admin_m_moduleTableLink(ModuleWithtableDescription),
minsert varchar(1),
mupdate varchar(1),
mdelete varchar(1),
createBy varchar(30)references admin_m_users(uid),
modifiedBy varchar(30)references admin_m_users(uid),
createdDate timestamp,
modifiedDate timestamp,
CHECK ((mread = 'Y' or mread = 'N' ) and (minsert = 'Y' or minsert = 'N') and (mupdate = 'Y' or mupdate = 'N')
and (mdelete = 'Y' or mdelete = 'N'))
)
Procedure to audo fill table id and create date:
CREATE OR REPLACE FUNCTION admin_after_insert_admin_m_roles()
RETURNS TRIGGER
AS $BODY$ BEGIN
UPDATE admin_m_roles
SET tableID = 'UserProfileadmin_m_roles', createdDate = current_timestamp
WHERE roleID = new.roleID;
RETURN NEW;
END;
$BODY$ LANGUAGE plpgsql;
trigger for above procedure:
CREATE TRIGGER admin_trigger_Afterinsert_admin_m_roles AFTER INSERT ON admin_m_roles
FOR EACH ROW EXECUTE PROCEDURE admin_after_insert_admin_m_roles();
Now delete procedure admin_after_insert_admin_m_users. (Keep following what i say here):
Delete trigger :admin_trigger_Afterinsert_admin_m_users
ALTER TABLE admin_m_users
ADD tableID varchar(40) references admin_m_moduleTableLink(ModuleWithtableDescription),
ADD createBy varchar(30)references admin_m_users(uid),
ADD modifiedBy varchar(30)references admin_m_users(uid),
ADD roleID varchar(30) references admin_m_roles(roleID);
Note: Now admin_m_users have role added to it along with others above.
Now recreate procedure and trigger for admin_m_users:
CREATE OR REPLACE FUNCTION admin_after_insert_admin_m_users()
RETURNS TRIGGER
AS $BODY$ BEGIN
IF new.createby IS NULL
THEN
UPDATE admin_m_users
SET createdDate = current_timestamp, tableID = 'UserProfileadmin_m_users', createby = new.uid, modifiedby = new.uid,
pwd = crypt(NEW.pwd, gen_salt('bf'))
WHERE uid = new.uid;
ELSE
UPDATE admin_m_users
SET createdDate = current_timestamp, tableID = 'UserProfileadmin_m_users',
pwd = crypt(NEW.pwd, gen_salt('bf'))
WHERE uid = new.uid;
END IF;
RETURN NEW;
END;
$BODY$ LANGUAGE plpgsql;
Trigger:
CREATE TRIGGER admin_trigger_Afterinsert_admin_m_users AFTER INSERT ON admin_m_users
FOR EACH ROW EXECUTE PROCEDURE admin_after_insert_admin_m_users();
Now link role with tables:
create table admin_m_module_usrRelation
(
tableID2 varchar(40) references admin_m_moduleTableLink(ModuleWithtableDescription),
tableID varchar(40) references admin_m_moduleTableLink(ModuleWithtableDescription), -- procedure
roleID varchar(30)references admin_m_roles(roleID),
createBy varchar(30)references admin_m_users(uid),
modifiedBy varchar(30)references admin_m_users(uid),
createdDate timestamp,
modifiedDate timestamp,
CONSTRAINT compositePair1 PRIMARY KEY (tableID2,roleID)
)
Usual procedure and trigger:
CREATE OR REPLACE FUNCTION admin_after_insert_admin_m_module_usrRelation()
RETURNS TRIGGER
AS $BODY$ BEGIN
UPDATE admin_m_module_usrRelation
SET tableID = 'UserProfileadmin_m_module_usrRelation', createdDate = current_timestamp
WHERE roleID = new.roleID and tableID2 = new.tableID2;
RETURN NEW;
END;
$BODY$ LANGUAGE plpgsql;
CREATE TRIGGER admin_trigger_Afterinsert_admin_m_module_usrRelation
AFTER INSERT ON admin_m_module_usrRelation
FOR EACH ROW
EXECUTE PROCEDURE admin_after_insert_admin_m_module_usrRelation();
Create Item Master, Restrict Item type to P(Parent), C(Child), PC(ParentAndChild):
create table admin_m_itemMaster
(
tableID varchar(40) references admin_m_moduleTableLink(ModuleWithtableDescription),
itemID varchar(30) primary key,
itemName varchar(40),
itemType varchar(2),
createBy varchar(30)references admin_m_users(uid),
modifiedBy varchar(30)references admin_m_users(uid),
createdDate timestamp,
modifiedDate timestamp,
totalCost float,
CHECK (itemType = 'P' or itemType = 'C' or itemType = 'PC')
)
Procedure to autolink it to registered table ID:
CREATE OR REPLACE FUNCTION admin_after_insert_admin_m_itemMaster()
RETURNS TRIGGER
AS $BODY$ BEGIN
UPDATE admin_m_itemMaster
SET tableID = 'Goodsadmin_m_itemMaster', createdDate = current_timestamp
WHERE itemID = new.itemID;
RETURN NEW;
END;
$BODY$ LANGUAGE plpgsql;
Trigger:
CREATE TRIGGER admin_trigger_Afterinsert_admin_m_itemMaster
AFTER INSERT ON admin_m_itemMaster
FOR EACH ROW
EXECUTE PROCEDURE admin_after_insert_admin_m_itemMaster();
Create Business Partener:
create table admin_m_businessPartner
(
tableID varchar(40) references admin_m_moduleTableLink(ModuleWithtableDescription),
BP_ID varchar(30) primary key,
BP_Type varchar(2),
BP_Name varchar(30),
createBy varchar(30)references admin_m_users(uid),
modifiedBy varchar(30)references admin_m_users(uid),
createdDate timestamp,
modifiedDate timestamp,
CHECK (BP_Type = 'C' or BP_Type = 'V' or BP_Type = 'CV')
)
CREATE OR REPLACE FUNCTION admin_after_insert_admin_m_businessPartner()
RETURNS TRIGGER
AS $BODY$ BEGIN
UPDATE admin_m_businessPartner
SET tableID = 'Partneradmin_m_businessPartner', createdDate = current_timestamp
WHERE BP_ID = new.BP_ID;
RETURN NEW;
END;
$BODY$ LANGUAGE plpgsql;
CREATE TRIGGER admin_trigger_Afterinsert_admin_m_businessPartner
AFTER INSERT ON admin_m_businessPartner
FOR EACH ROW
EXECUTE PROCEDURE admin_after_insert_admin_m_businessPartner();
Create a BOM table. This table links parent and child items and with relevant metrics. I have written the procedure in a such way that a parent Item should be registered as type 'P' in admin_m_item table:
create table admin_m_itemBOM
(
itemID varchar(30) NOT NULL,
parentID varchar(30) NOT NULL,
quantity int,
quantityUnit varchar(20),
price float,
vendorID varchar(30),
tableID varchar(40) references admin_m_moduleTableLink(ModuleWithtableDescription),
createBy varchar(30)references admin_m_users(uid),
modifiedBy varchar(30)references admin_m_users(uid),
createdDate timestamp,
modifiedDate timestamp,
CONSTRAINT itemID PRIMARY KEY (itemID,parentID),
CONSTRAINT itemIDF FOREIGN KEY (itemID)
REFERENCES admin_m_itemMaster (itemID),
CONSTRAINT parentIDF FOREIGN KEY (parentID)
REFERENCES admin_m_itemMaster (itemID),
CONSTRAINT vendorID FOREIGN KEY (vendorID)
REFERENCES admin_m_businessPartner (BP_ID)
)
Procedure:
CREATE OR REPLACE FUNCTION admin_after_insert_admin_m_itemBOM()
RETURNS TRIGGER
AS $BODY$ BEGIN
UPDATE admin_m_itemBOM
SET tableID = 'Goodsadmin_m_itemBOM',createdDate = current_timestamp
WHERE (itemID = new.itemID and parentID = new.parentID) and parentID IN (select itemID from admin_m_itemMaster where itemType = 'P');
delete from admin_m_itemBOM where tableID IS NULL;
RETURN NEW;
END;
$BODY$ LANGUAGE plpgsql;
Note: If parent ID is not among registered parent item, i delete the row in the above procedure.
Trigger to run above procedure:
CREATE TRIGGER admin_trigger_Afterinsert_admin_m_itemBOM
AFTER INSERT ON admin_m_itemBOM
FOR EACH ROW
EXECUTE PROCEDURE admin_after_insert_admin_m_itemBOM();
