Trigger to update and insert - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Trigger to update and insert

Author

Topic

PAP069
Starting Member

3 Posts

Posted - 2009-02-12 : 05:53:10

Hi there, sorry if my post is out of place!

I am new to triggers so any quidance would be greatly appreciated.

My scenario is as follows:
I have two tables:
Employees:
EMPLOYEE_ID --> PK
COMPANY_ID --> PK
MEDICAL_ID
FIRST_NAME
SURNAME
COST_CENTER
ID_CARD_NO
SOC_INS_NO
TAX_ID_CODE
GENDER
MARITAL_STATUS
BIRTH_DATE
EMPLOYMENT_DATE1
TerminationDate
STATUS

and

Employeers Liability:
EmployeersLiabilityID--> PK (is the concatenation of Company_id and Employee_id
COMPANY_ID----> FK on Employees
EMPLOYEE_ID----> FK on Employees
EmpLiaIns (bit)-----> default value of 1

The trigger must reside on the employees table to handle the following scenario:

For any new record in Employees table (maybe multiple rows at once)
and if employee.status = 'Active'
then insert into EmployeersLiability table: EmployeersLiabilityID, Company_id,Employee_id and set EmpLiaIns to 1 (True)

For any update on Employees.status ='Terminated'
set EmployeersLiability.EmpLiaIns to 0 'False' based of cource on the foreign keys.

I hope you can understand my requirement and help me resolve it.
Thanxs in advance
Panos

nr
SQLTeam MVY

12543 Posts

Posted - 2009-02-12 : 06:18:10

create trigger tr_Employees on Employees for insert, update
as
if not exists (select * from deleted)
begin
-- insert
insert EmployeersLiability (EmployeersLiabilityID, Company_id,Employee_id,EmpLiaIns)
select EmployeersLiabilityID, Company_id,Employee_id,1
from inserted
where status ='active'
end
else
begin
-- updates
update EmployeersLiability
set EmpLiaIns = 0
from EmployeersLiability t
join inserted i
on t.EmployeersLiabilityID = i.EmployeersLiabilityID
and ....
where i.status ='Terminated'
end

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

PAP069
Starting Member

3 Posts

Posted - 2009-02-12 : 07:29:24

Nr thank you for your quidance, the trigger works as expected.
Two questions though, can the trigger handle multiple updates and inserts from the employees table?
And secondly, what if the employee that got 'terminated' got back 'Active' how can i manage this in the update section of the trigger?
Thanks again

ALTER trigger [dbo].[tr_Employees] on [dbo].[Employees] for insert, update
as
if not exists (select * from deleted)
begin
-- insert
insert [Employeers Liability] (EmployeersLiabilityID, Company_id,Employee_id,EmpLiaIns)
select COMPANY_ID+EMPLOYEE_ID as EmployeersLiabilityID, Company_id,Employee_id,1
from inserted
where status ='active'
end
else
begin
-- updates
update [Employeers Liability]
set EmpLiaIns = 0
from [Employeers Liability] t
join inserted i
on t.company_id = i.company_id
and t.employee_id=i.employee_id
where i.status ='Terminated'
end

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-12 : 10:04:08

it will work for multiple insert/updates.

for second question,just modify like below


ALTER trigger [dbo].[tr_Employees] on [dbo].[Employees] for insert, update
as
if not exists (select * from deleted)
begin
-- insert
insert [Employeers Liability] (EmployeersLiabilityID, Company_id,Employee_id,EmpLiaIns)
select COMPANY_ID+EMPLOYEE_ID as EmployeersLiabilityID, Company_id,Employee_id,1
from inserted
where status ='active'
end
else if update(status)
begin 
update [Employeers Liability] 
set EmpLiaIns = case when i.status ='Terminated' then 0 
                     when i.status ='Active' then 1 
                end
from [Employeers Liability] t
join inserted i
on t.company_id = i.company_id
and t.employee_id=i.employee_id
end

PAP069
Starting Member

3 Posts

Posted - 2009-02-16 : 02:52:35

Thanxs for all your help and support.
I have two scenarios that need to be added to the triger. When the status is updated to Terminated, i want the termination date from employees table to update the corresponding collumn in the employeersLiability table.

The second scenario is the same as above but to update the column with the 15th of the month that is displayed in the employees table!

The fisrt scenario will be applied to the following triger and the other one to a different one.

How can i alter the trigger?

ALTER trigger [dbo].[tr_Employees] on [dbo].[Employees] for insert, update
as
if not exists (select * from deleted)
begin
-- insert
insert [Employeers Liability] (EmployeersLiabilityID, Company_id,Employee_id,AcceptanceDate,TerminationDate,EmpLiaIns)
select COMPANY_ID+EMPLOYEE_ID as EmployeersLiabilityID, Company_id,Employee_id,EMPLOYMENT_DATE1,TerminationDate,1
from inserted
where status ='active'
end
else if update(status)
begin
update [Employeers Liability]
set EmpLiaIns = case when i.status ='Terminated' then 0
when i.status ='Active' then 1
end
from [Employeers Liability] t
join inserted i
on t.company_id = i.company_id
and t.employee_id=i.employee_id
end

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources