Click here to Skip to main content
14,927,528 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
if I Add new column at employee table which contains default value like
SQL
create table employee(empid [varchar](10),empname [varchar](50),salary [decimal](18, 2),status [varchar] (100)(default ''))

create table employeeHistory(EffectDate [datetime],empid [varchar](10),empname [varchar](50),salary [decimal](18, 2),status [varchar] (100)(default ''))

create trigger UpdateEmployee on employee
after update AS
begin
declare @empid as [varchar](10)
declare @empname as [varchar](50)
declare @salary as [decimal](18, 2)
declare @status as [varchar](100)

select @empid =d.empid from deleted d
select @empname =d.empname from deleted d
select @salary =d.salary from deleted d


if update(status)
select @status=i.statusfrom deleted i
else
begin
select @status=i.status from deleted i
update [employee]
set status='' where empid=@empid
end

Insert into employeeHistory([EffectDate],[empid],[empname],[salary],[status])
SELECT getdate(), empid, empname, salary,status
FROM deleted
end



Example: emp001 xxxx 10000.00 paid
emp002 yyyy 12000.00 paid
emp003 zzzz 10000.00 paid
emp004 aaaa 15000.00 paid
emp005 bbbb 12000.00 paid
emp006 cccc 12000.00 paid
emp007 pppp 10000.00 paid

update employee set salary =11000.00 where salary =10000.00
after update first updated row set its default value but rest rows don't set it in employee table. Please give me a solution at your earliest.
Posted
Updated 30-Apr-15 8:22am
v2
Comments
Corporal Agarn 30-Apr-15 13:49pm
   
You seem to be treating the trigger as if it only worked on one row at a time. Example select @empid =d.empid from deleted d can fail because there are multiple d.empid
ZurdoDev 30-Apr-15 14:30pm
   
I'm not sure what you are asking. Defaults are only for when the row is created.
RTK The Limited Edition 30-Apr-15 14:36pm
   
Just think client want this
ZurdoDev 30-Apr-15 14:36pm
   
That does not even come close to addressing what I said, does it?
ZurdoDev 30-Apr-15 14:37pm
   
I have no idea what you are asking for.

1 solution

Hi,

May be bellow code will work for you...

USE [MyTestDB]
GO
/****** Object: Trigger [dbo].[UpdateEmployee] Script Date: 05/04/2015 17:34:43 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER trigger [dbo].[UpdateEmployee] on [dbo].[employee]
after update AS
begin
declare @empid as [varchar](10)
declare @empname as [varchar](50)
declare @salary as [decimal](18, 2)
declare @status as [varchar](100)
declare @empids as [varchar](max)

select @empid =d.empid from deleted d
select @empname =d.empname from deleted d
select @salary =d.salary from deleted d

print @empids
print @empid

if update(status)
begin
Select @status=i.status from deleted i
--print 'if'
end
else
begin
--print 'else'
select @status=i.status from deleted i
--select * from [employee] where empid in (SELECT empid FROM inserted)
update [employee]
set status='' where empid in (SELECT empid FROM inserted)
end
--print 'deleted'
--Select * from deleted
Insert into employeeHistory([EffectDate],[empid],[empname],[salary],[status])
SELECT getdate(), empid, empname, salary,status
FROM deleted
end
   
v2

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




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900