Solved

Update table after an Insert

Posted on 2012-04-09
9
251 Views
Last Modified: 2012-04-15
I am looking for a trigger that will update my table after an Insert.

Table to run trigger on "TEMP"
When a record is entered I want to take the Field "MTSS_NAME" and go to the table called "ANALYST"

Analyst Table Contains the Fields "EMAIL" and "ManagerName"

Find all of the Email with the "ManagerName = to Mtss_Name" and create a string separated with a ";" and update the table "TEMP" with that string into the field "DistrictDL"

Can someone help Please.
0
Comment
Question by:xeroxcanada
9 Comments
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 37824367
zeroxcanada,

With respect, that is not a good idea.  For example, while this can be done to get the value correct at the INSERT time, what happens if later on the emails on the analyst table change?

You are better off to instead use a select query at the time you need the data.  This is rather easily done using FOR XML PATH, assuming you are on SQL 2005 or later.

Patrick
0
 
LVL 2

Author Comment

by:xeroxcanada
ID: 37824398
Sorry but I am really green here.
I need to update the record as it is entered into the database.
The emails are inserted once.
0
 
LVL 27

Expert Comment

by:tliotta
ID: 37826027
If it really must be done, I would use a BEFORE INSERT trigger in DB2 to set the value of the column before it gets written to the table. I'm not sure of a good reason for doing it as an update after it's written.

I don't have appropriate experience with SQL Server to provide a reliable example.

Tom
0
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 
LVL 2

Accepted Solution

by:
xeroxcanada earned 0 total points
ID: 37828471
Final trigger that works.

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go


ALTER TRIGGER [IS_USA]
ON [dbo].[temp]
AFTER INSERT
AS

DECLARE @p1 varchar (50)  
DECLARE @p2 varchar (10)


BEGIN
  SELECT                        
      @p1 = MTSS_Name,
      @p2 = Escal_ID,

FROM Inserted

      -- SET NOCOUNT ON added to prevent extra result sets from
      -- interfering with SELECT statements.
      SET NOCOUNT ON;

    -- Insert statements for procedure here

INSERT temp_email (Entry, Email)

SELECT Entry, Email
FROM Analyst  
WHERE ManagerName = @p1

--(select * from temp_email)

declare @sqlstr varchar(max),
            @crtid integer, @Getmail varchar (max)

set  @sqlstr =  ''

DECLARE email_list CURSOR FOR (SELECT Entry  FROM temp_email)
OPEN email_list
      FETCH next FROM email_list INTO @crtid
      WHILE @@fetch_status=0
      BEGIN
            set @sqlstr = @sqlstr + (select email from temp_email where Entry = @crtid) + ';'
      FETCH next FROM email_list INTO @crtid
      END
CLOSE email_list
DEALLOCATE email_list
DELETE temp_email
--Print @sqlstr
EXEC CreateDL @sqlstr,@p2

END
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 37830508
I realize you are have stated you are "really green here", however, you should know that TRIGGERs fire once per statement and not once per row.  Clearly the way you have it coded does not apply when more than one row is INSERTed.
0
 
LVL 2

Author Comment

by:xeroxcanada
ID: 37832234
I Need to understand this a bit more.
I have a form that does an Insert into the DB and inserts a new row.
So when that happens the trigger fires.

So if the form is sent twice lets say at the same time will the trigger fire twice or once?
The chance of someone filling in the form at the exact same time is very minimal.
I only get about 30 inserts in an 8-12 hour period.
So what am I missing?
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 37832434
So what am I missing?
If your INSERT statement inserts more than one row, the TRIGGER will fire only once and in consequence @p1 and @p2 will contain the values from one row, even though INSERTED contains more than one row. If this will never happen (a single user INSERTs more than one row at one time) than you should be able to sleep fine.
0
 
LVL 2

Author Comment

by:xeroxcanada
ID: 37832630
zzzzzzzzzzzzzzzzzzzzzzzzzzz...Thank You for clearing that up.
0
 
LVL 2

Author Closing Comment

by:xeroxcanada
ID: 37847992
Figured it out
0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

Suggested Solutions

Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

828 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