Trigger coding

Hi, i am trying to rearrange data in new table upon INSERT active in "BREAK_TBL"

Current application do have the following options which written in c# to determine working hours.

                            Start Time                                      End Time                                     CheckBox
Working Hrs      2018-09-05 08:15                          2018-09-05 19:50                      True      (if unchecked then default working hrs are from 08:15 ~ 17:15)
Break 1              2018-09-05 10:10                          2018-09-05 10:35                       True
Break 2              2018-09-05 12:00                          2018-09-05 12:35                       True
Break 3              2018-09-05 15:45                          2018-09-05 16:00                       True
Break 4              2018-09-05 17:20                          2018-09-05 17:50                       True
Break 5              2018-09-05 10:10                          2018-09-05 10:10                       False

The above data area successfully updates in MariaDB in "BREAK_TBL". Upon Insert into BREAK_TBL , I have to trigger another table for my further analysis. Its suppose to arrange in the following format to determine my working our period for every interval.

                            Start Time                                      End Time                              CheckBox
Set 1              2018-09-05 08:15                          2018-09-05 10:10                       True            (start work at 08:15 and Break 1 start at 10:10)
Set 2              2018-09-05 10:35                          2018-09-05 12:00                       True            (Break1 over at 10:35 and become start working hour and end at 12:00 which start Break 2)
Set 3              2018-09-05 12:35                          2018-09-05 15:45                       True
Set 4              2018-09-05 16:00                          2018-09-05 17:20                       True
Set 5              2018-09-05 17:50                          2018-09-05 19:50                       True


I am stuck at bringing data from row 2 - Break 1 to end work in row 1.
Rama TitoProgrammerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ste5anSenior DeveloperCommented:
I would not do this with a trigger. Use a stored procedure to consolidate the information. Cause a trigger must eventually rearrange rows and thus delete rows. Which is the wrong place to do this, cause the normal way is to drop all related sets and recalculate them.

Thus call a stored procedure in your application, after all changes are made. And consider doing a daily recalculation of the entire set table.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Rama TitoProgrammerAuthor Commented:
Hi Expert

The maximum level of calculation only once per day.

BEGIN
      DECLARE @Count int
      DECLARE @STemp1, STemp2, STemp3, STemp4, STemp5 timestamp
      DECLARE @ETemp1, ETemp2, ETemp3, ETemp4, ETemp5 timestamp
      
      IF CheckBox1 = True
            @STemp1  = SELECT STARTTIME From BREAK_TBL WHERE ID=1
            @ETemp1  = SELECT STARTTIME From BREAK_TBL WHERE ID=1
            INSERT INTO WORKINGTBL (StartTIME, EndTiME) VALUES (@STemp1, @ETemp1)
      
      IF CheckBox2 = True
            @STemp2  = SELECT STARTTIME From BREAK_TBL WHERE ID=2
            @ETemp3  = SELECT STARTTIME From BREAK_TBL WHERE ID=2
            INSERT INTO WORKINGTBL (StartTIME, EndTiME) VALUES (@STemp3, @ETemp1)
            
            INSERT INTO WORKINGTBL (EndTiME) VALUES (@STemp2) Where ID = 1 # I am trying to swap the end time.
            
      If ChcekBox3 = True
      If CheckBox4 = True
      If CheckBox5 = True
      If CheckBox6 = True
0
Rama TitoProgrammerAuthor Commented:
By doing so I do manage to resolve the issue.

BEGIN

DECLARE temp TIMESTAMP;
DECLARE temp1 TIMESTAMP;
DECLARE netSEC INT;
DECLARE startTime1 TIMESTAMP;
DECLARE endTime1 TIMESTAMP;
DECLARE startTime2 TIMESTAMP;
DECLARE endTime2 TIMESTAMP;

IF NEW.idBRK = 1 THEN

	insert into workingHRS(idREF,startTM,endTM,sec)values(NEW.id,NEW.wrkStart,NEW.wrkEnd,0);
	

END IF;

IF NEW.idBRK  = 2 THEN

	SET temp1 = (select endTM from workingHRS where id = 1);
	insert into workingHRS(idREF,startTM,endTM,sec)values(NEW.id,NEW.wrkEnd,temp1,0);
	update workingHRS set endTM = NEW.wrkStart where idREF = 1;
		
END IF;

IF NEW.idBRK = 3 THEN

	SET temp1 = (select endTM from workingHRS where id = 2);
	insert into workingHRS(idREF,startTM,endTM,sec)values(NEW.id,NEW.wrkEnd,temp1,0);
	update workingHRS set endTM = NEW.wrkStart where idREF = 2;
		
END IF;

IF NEW.idBRK = 4 THEN

	SET temp1 = (select endTM from workingHRS where id = 3);
	insert into workingHRS(idREF,startTM,endTM,sec)values(NEW.id,NEW.wrkEnd,temp1,0);
	update workingHRS set endTM = NEW.wrkStart where idREF = 3;
		
END IF;

IF NEW.idBRK  = 5 THEN

	SET temp1 = (select endTM from workingHRS where id = 4);
	insert into workingHRS(idREF,startTM,endTM,sec)values(NEW.id,NEW.wrkEnd,temp1,0);
	update workingHRS set endTM = NEW.wrkStart where idREF = 4;
		
END IF;

Open in new window

0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.