?
Solved

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

Posted on 2009-05-18
4
Medium Priority
?
159 Views
Last Modified: 2012-05-07
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
Comment
Question by:MaithreeD
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 1

Assisted Solution

by:venkatca
venkatca earned 450 total points
ID: 24419217
remove this line and it should work

if (@pmcode  != null)
0
 
LVL 1

Assisted Solution

by:venkatca
venkatca earned 450 total points
ID: 24419230
if you still need to check null values try this instead

if isnull(@pmcode,'')<>''
0
 
LVL 22

Assisted Solution

by:pivar
pivar earned 450 total points
ID: 24419235
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
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 600 total points
ID: 24419267
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

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question