PL_SQL_triggers.txt

-----password hashing------------------------------------------------------------------------
-----when inserting or updating password----
CREATE OR REPLACE TRIGGER PASSWORD_HASH
BEFORE INSERT OR UPDATE
--OF PASSWORD
ON ACCOUNT
FOR EACH ROW

DECLARE
	HASHED_PASS VARCHAR2(30);
	TEMP VARCHAR2(30);
	
BEGIN
	TEMP := :NEW.PASSWORD;
	SELECT ORA_HASH(TEMP) INTO HASHED_PASS FROM DUAL;
	dbms_output.put_line(HASHED_PASS);
	:NEW.PASSWORD := HASHED_PASS;
EXCEPTION
	WHEN NO_DATA_FOUND THEN
		DBMS_OUTPUT.PUT_LINE('NO DATA FOUND') ;
	WHEN TOO_MANY_ROWS THEN
		DBMS_OUTPUT.PUT_LINE('MORE THAN ONE DATA FOUND') ;
	WHEN OTHERS THEN
		DBMS_OUTPUT.PUT_LINE('UNKNOWN ERROR OCCURED') ;
END;
/
-------------------------------------------------------------------------------------------------------------



--------Welcome Message from bikroy admin to user after sign up-----------------------------------------------------------
CREATE OR REPLACE TRIGGER WELCOME_MSG
AFTER INSERT
ON ACCOUNT
FOR EACH ROW

DECLARE
	MSG_TO VARCHAR2(20);
BEGIN
	MSG_TO := :NEW.USERNAME; 
	INSERT INTO chat 
	VALUES(CHAT_SEQUENCE.NEXTVAL, 'Dear '||MSG_TO||', Welcome to BIKROY.COM, Thank you for your subsciption to our website. Enjoy buying and selling with us. You will get notified with this thread while necessary. Have a good day!', SYSDATE,  'bikroy', MSG_TO);
	dbms_output.put_line('USER IS WELCOMED');

EXCEPTION
	WHEN NO_DATA_FOUND THEN
		DBMS_OUTPUT.PUT_LINE('NO DATA FOUND') ;
	WHEN TOO_MANY_ROWS THEN
		DBMS_OUTPUT.PUT_LINE('MORE THAN ONE DATA FOUND') ;
	WHEN OTHERS THEN
		DBMS_OUTPUT.PUT_LINE('UNKNOWN ERROR OCCURED') ;
END;
/
----------------------------------------------------------------------------------------------------------------------------





---------------Submission of Prod Ad notification msg (before confirmation)---------------------------------------------------------------
CREATE OR REPLACE TRIGGER PRODUCT_AD_SUBMISSION_MSG
AFTER INSERT
ON PRODUCT
FOR EACH ROW

DECLARE
	MSG_TO VARCHAR2(20);
	AD_ID VARCHAR2(20);
	PROD_ID VARCHAR2(20);
	PROD_NAME VARCHAR2(200);
	MSG_CONT VARCHAR2(2000);
	
BEGIN
	
	AD_ID := :NEW.ADVERTISEMENT_ID;
	
	PROD_ID := :NEW.PRODUCT_ID;
	PROD_NAME := :NEW.PRODUCT_NAME;
	
	MSG_TO := USERNAME_FROM_AD_ID(AD_ID);
	
	MSG_CONT := 'Dear '||MSG_TO||', We have received your advertisement. Your advertisement_id is '||AD_ID||'. Your posted product_id is '||PROD_ID||' and product_name is '||PROD_NAME||'. Please wait for admin approval for your advertisement before posting. You will be notified with this thread. Feel free to ask any kind of question about your advertisement. Enjoy buying and selling with us. Have a good day!';
	
	INSERT INTO chat 
	VALUES(CHAT_SEQUENCE.NEXTVAL, MSG_CONT, SYSDATE,  'bikroy', MSG_TO);
	dbms_output.put_line('USER HAS POSTED AD');
EXCEPTION
	WHEN NO_DATA_FOUND THEN
		DBMS_OUTPUT.PUT_LINE('NO DATA FOUND') ;
	WHEN TOO_MANY_ROWS THEN
		DBMS_OUTPUT.PUT_LINE('MORE THAN ONE DATA FOUND') ;
	WHEN OTHERS THEN
		DBMS_OUTPUT.PUT_LINE('UNKNOWN ERROR OCCURED') ;
END;
/
-----------------------------------------------------------------------------------------------------------------------------------------------



---------------Submission of Job Ad notification msg (before confirmation) -------------------------------------------------------------------
CREATE OR REPLACE TRIGGER JOB_AD_SUBMISSION_MSG
AFTER INSERT
ON JOB
FOR EACH ROW

DECLARE
	MSG_TO VARCHAR2(20);
	AD_ID VARCHAR2(20);
	J_ID VARCHAR2(20);
	J_NAME VARCHAR2(500);
	MSG_CONT VARCHAR2(2000);
	
BEGIN
	
	AD_ID := :NEW.ADVERTISEMENT_ID;
	
	J_ID := :NEW.JOB_ID;
	
	J_NAME := :NEW.JOB_TYPE ||' - '||:NEW.DESIGNATION||' of APPROX SALARY - '||:NEW.SALARY;
	
	MSG_TO := USERNAME_FROM_AD_ID(AD_ID);
	
	MSG_CONT := 'Dear '||MSG_TO||', We have received your advertisement. Your ADVERTISEMENT ID is '||AD_ID||'. Your posted JOB ID is '||J_ID||' and JOB is '||J_NAME||'. Please wait for admin approval for your advertisement before posting. You will be notified with this thread. Feel free to ask any kind of question about your advertisement. Enjoy buying and selling with us. Have a good day!';
	
	INSERT INTO chat 
	VALUES(CHAT_SEQUENCE.NEXTVAL, MSG_CONT, SYSDATE,  'bikroy', MSG_TO);
	dbms_output.put_line('USER HAS POSTED AD');
EXCEPTION
	WHEN NO_DATA_FOUND THEN
		DBMS_OUTPUT.PUT_LINE('NO DATA FOUND') ;
	WHEN TOO_MANY_ROWS THEN
		DBMS_OUTPUT.PUT_LINE('MORE THAN ONE DATA FOUND') ;
	WHEN OTHERS THEN
		DBMS_OUTPUT.PUT_LINE('UNKNOWN ERROR OCCURED') ;
END;
/
---------------------------------------------------------------------------------------------------------------------------------------




---------------JOB AD CONFIRMED BY ADMIN MSG ------------------------------------------------------------------------------------------
CREATE OR REPLACE TRIGGER JOB_AD_CONFIRMATION_MSG
AFTER UPDATE
--OF ADVERTISEMENT_TYPE
ON ADVERTISEMENT
FOR EACH ROW

DECLARE
	MSG_TO VARCHAR2(20);
	MSG VARCHAR2(2000);
	AD_ID VARCHAR2(20);

	J_ID VARCHAR2(20);
	J_NAME VARCHAR2(500);
	
BEGIN
	MSG_TO := :NEW.USERNAME; 
	AD_ID := :NEW.ADVERTISEMENT_ID;
	
	J_ID := JOB_ID_FROM_AD_ID(AD_ID);
	dbms_output.put_line(J_ID);
	J_NAME := JOB_NAME_FROM_JOB_ID(J_ID);
	dbms_output.put_line(J_NAME);
	
	MSG := 'Dear '||MSG_TO||', Your previously submitted job advertisement of '||J_NAME||' has been approved by bikroy-admin and posted on bikroy.com wall. Your submitted JOB ID is '||J_ID||'. Feel free to ask any kind of question about your advertisement. Enjoy buying and selling with us. Have a good day!';
	INSERT INTO chat 
	VALUES(CHAT_SEQUENCE.NEXTVAL, MSG, SYSDATE,  'bikroy', MSG_TO);
	dbms_output.put_line('JOB AD POSTED TO BIKROY WALL');

	
EXCEPTION
	WHEN NO_DATA_FOUND THEN
		DBMS_OUTPUT.PUT_LINE('NO DATA FOUND') ;
	WHEN TOO_MANY_ROWS THEN
		DBMS_OUTPUT.PUT_LINE('MORE THAN ONE DATA FOUND') ;
	WHEN OTHERS THEN
		DBMS_OUTPUT.PUT_LINE('UNKNOWN ERROR OCCURED') ;
END;
/
-----------------------------------------------------------------------------------------------------------------------------------------



---------------PRODUCT AD CONFIRMED BY ADMIN MSG ----------------------------------------------------------------------------------------
CREATE OR REPLACE TRIGGER PROD_AD_CONFIRMATION_MSG
AFTER UPDATE
--OF ADVERTISEMENT_TYPE
ON ADVERTISEMENT
FOR EACH ROW

DECLARE
	MSG_TO VARCHAR2(20);
	MSG VARCHAR2(2000);
	AD_ID VARCHAR2(20);

	PROD_ID VARCHAR2(20);
	PROD_NAME VARCHAR2(200);
	
BEGIN
	MSG_TO := :NEW.USERNAME; 
	AD_ID := :NEW.ADVERTISEMENT_ID;
	
	PROD_ID := PROD_ID_FROM_AD_ID(AD_ID);
	dbms_output.put_line(PROD_ID);
	PROD_NAME := PROD_NAME_FROM_PROD_ID(PROD_ID);
	dbms_output.put_line(PROD_NAME);
	
	MSG := 'Dear '||MSG_TO||', Your previously submitted product advertisement of '||PROD_NAME||' has been approved by bikroy-admin and posted on bikroy.com wall. Your submitted PRODUCT ID is '||PROD_ID||'. Feel free to ask any kind of question about your advertisement. Enjoy buying and selling with us. Have a good day!'; 
	INSERT INTO chat 
	VALUES(CHAT_SEQUENCE.NEXTVAL, MSG, SYSDATE, 'bikroy', MSG_TO);
	dbms_output.put_line('PRODUCT AD POSTED TO BIKROY WALL');

	
EXCEPTION
	WHEN NO_DATA_FOUND THEN
		DBMS_OUTPUT.PUT_LINE('NO DATA FOUND') ;
	WHEN TOO_MANY_ROWS THEN
		DBMS_OUTPUT.PUT_LINE('MORE THAN ONE DATA FOUND') ;
	WHEN OTHERS THEN
		DBMS_OUTPUT.PUT_LINE('UNKNOWN ERROR OCCURED') ;
END;
/
----------------------------------------------------------------------------------------------------------------------------------------------