[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 163
  • Last Modified:

How do I access values from the inserted and deleted tables ,when wrting a trigger

How do I access values from the inserted and deleted tables ,when wrting a trigger?
Consider the following trigger written on table CONFIG_PROJECTS.The trigger is created successfully but doesnt fire.


ALTER TRIGGER [dbo].[UTRG_InitializeResources] 
   ON   [dbo].[CONFIG_PROJECTS] 
   FOR INSERT,UPDATE 
AS 
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
 
    -- Insert statements for trigger here
 
declare @pmcode varchar(10)
select @pmcode =(select str_empcode_PM  from inserted)
if (@pmcode  != null)
insert into TRANS_RESOURCES values(4,@pmcode,null,'WSZ001',40.3,1)
 
END

Open in new window

0
MaithreeD
Asked:
MaithreeD
  • 2
4 Solutions
 
venkatcaCommented:
remove this line and it should work

if (@pmcode  != null)
0
 
venkatcaCommented:
if you still need to check null values try this instead

if isnull(@pmcode,'')<>''
0
 
pivarCommented:
Hi,

Since an insert/update can affect multiple rows change your trigger like this. Otherwise this should run fine on inserts and update to the table CONFIG_PROJECTS where str_empcode_PM
is not null.

/peter


ALTER TRIGGER [dbo].[UTRG_InitializeResources] 
   ON   [dbo].[CONFIG_PROJECTS] 
   FOR INSERT,UPDATE 
AS 
BEGIN
        -- SET NOCOUNT ON added to prevent extra result sets from
        -- interfering with SELECT statements.
        SET NOCOUNT ON;
 
    -- Insert statements for trigger here
 
insert into TRANS_RESOURCES 
select 4,str_empcode_PM,null,'WSZ001',40.3,1  
from inserted
where str_empcode_PM!= null
 
END

Open in new window

0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
where str_empcode_PM!= null
will not work, unless you had ANSI_NULLS set to OFF.

instead:
where str_empcode_PM IS NOT null
0

Featured Post

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now