update the information for two tables

Question

1.00/5 (1 vote)

See more:

leave_type table application table sanction table leave details tabel

leavetypeid empid empid empid
leavetypename startdate leavetype totalleave
noofleaves enddate noofleaves leavetype
leavetype status usedleave
noofleaes remainingleave

allignment is not proper first row is the first table like leave_type table like rest of the table

in this leave managmnt project, once application is sent to the hod after approving the leave depending on the status request or reject all the leave information stored in the leave details table like remaing leave,used leave... used leave each time upgrade... please write the query for that.

Posted 1-Feb-15 15:41pm

Member 10711518

Updated 1-Feb-15 15:48pm

v2

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Sumit Jawale · Answer 1 · 2015-02-03T17:53:00

Solution 2

Hi,
Please try below trigger on your leave table (in which you are going to updated your Leave Status)

SQL

Create Trigger UpdateLeaveDetails on [Leave]
For update
as
Declare @empid int;
Declare @LeaveStatus int;

select @empid=i.Emp_ID from inserted i;	
select @LeaveStatus=i.LeaveStatus from inserted i;

if (@LeaveStatus == 1)  -- 1 for approve
		UPDATE LeaveDetails SET remaing_leave = remaing_leave-1 ,used_leave = used_leave-1 where empid = @empid
		
	-- No Need to update LeaveDetails information if leave are rejected  
GO

Posted 3-Feb-15 17:53pm

Sumit Jawale

Updated 3-Feb-15 18:01pm

v4

Comments

Sumit Jawale 4-Feb-15 0:32am

Here you can take @LeaveStatus as varchar and you cna check if (@LeaveStatus == 'Approve')

Member 10711518 4-Feb-15 2:13am

thank u.

without using the trigger.how to write the normal innerjoin or subquery for above table.

Sumit Jawale 4-Feb-15 7:00am

Hi, What exactly you want to do ? What i understood from your comment is if hod approves leave, then you need to update the remaining record count.Correct ??

If you dont want to use trigger then you can write two update statments to update leave status and to update remaining leaves.

Sumit Jawale · Answer 2 · 2015-02-01T20:22:00

Solution 1

Hi,
You can use trigger to update two tables.

Posted 1-Feb-15 20:22pm

Sumit Jawale

Comments

Member 10711518 3-Feb-15 21:30pm

please help me query for that