[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Can i execute both insert and update statements in the same 'instead of update' trigger of a view

Posted on 2006-04-03
8
Medium Priority
?
231 Views
Last Modified: 2012-08-13
I have a trigger that needs to be executed when data in a view is updated.

If the IncurredAmount field in my view is updated, i need a new record to be created in the IncurredAmount table as well as updating the other fields in the view. However when I try and save the view I get an error message saying sub queries are not allowed.

Is it possible to do this (my code follows)? Also, how can i update fields with an NText data type?

CREATE TRIGGER [TriggerUpdate_LossSelectLoss] ON [LossSelectLoss] INSTEAD OF UPDATE
AS
      BEGIN
            IF UPDATED(IncurredAmount)
                  BEGIN
                        INSERT INTO dbo.IncurredAmount
                        (
                              LossID,
                              RegisteredUserID,
                              IncurredAmount

                        )
                        VALUES
                        (
                              (SELECT inserted.LossID FROM inserted),
                              2,
                              (SELECT inserted.IncurredAmount FROM inserted)

                        )

                  END


            UPDATE [LossSelectLoss]
            SET
                  ClaimReference = (SELECT inserted.ClaimReference FROM inserted),
                  LossDateTime = (SELECT inserted.LossDateTime FROM inserted),
                  ReceivedDateTime = (SELECT inserted.ReceivedDateTime FROM inserted),
                  ServiceID = (SELECT inserted.ServiceID FROM inserted),
                  AssuredID = (SELECT inserted.AssuredID FROM inserted),
                  CurrencyID = (SELECT inserted.CurrencyID FROM inserted),
                  LimitAmount = (SELECT inserted.LimitAmount FROM inserted),
                  DeductibleAmount = (SELECT inserted.DeductibleAmount FROM inserted),
                  CreditedAmount = (SELECT inserted.CreditedAmount FROM inserted),
                  OutstandingAmount = (SELECT inserted.OutstandingAmount FROM inserted),
                  
                  StatusID = (SELECT inserted.StatusID FROM inserted),
                  LossRegisteredUserID = (SELECT inserted.LossRegisteredUserID FROM inserted),
                  DiaryDateTime = (SELECT inserted.DiaryDateTime FROM inserted)

            WHERE LossID = (SELECT inserted.LossID FROM inserted)
            
      END

0
Comment
Question by:andichilds
  • 3
  • 2
  • 2
  • +1
8 Comments
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 1200 total points
ID: 16359796
This should work better:

CREATE TRIGGER [TriggerUpdate_LossSelectLoss] ON [LossSelectLoss] INSTEAD OF UPDATE
AS
     BEGIN
          IF UPDATED(IncurredAmount)
               BEGIN
                    INSERT INTO dbo.IncurredAmount
                    (
                         LossID,
                         RegisteredUserID,
                         IncurredAmount
                    )
                    SELECT inserted.LossID,2, inserted.IncurredAmount FROM inserted
               END


          UPDATE [LossSelectLoss]
          SET
               ClaimReference = (SELECT inserted.ClaimReference FROM inserted),
               LossDateTime = (SELECT inserted.LossDateTime FROM inserted),
               ReceivedDateTime = (SELECT inserted.ReceivedDateTime FROM inserted),
               ServiceID = (SELECT inserted.ServiceID FROM inserted),
               AssuredID = (SELECT inserted.AssuredID FROM inserted),
               CurrencyID = (SELECT inserted.CurrencyID FROM inserted),
               LimitAmount = (SELECT inserted.LimitAmount FROM inserted),
               DeductibleAmount = (SELECT inserted.DeductibleAmount FROM inserted),
               CreditedAmount = (SELECT inserted.CreditedAmount FROM inserted),
               OutstandingAmount = (SELECT inserted.OutstandingAmount FROM inserted),
               
               StatusID = (SELECT inserted.StatusID FROM inserted),
               LossRegisteredUserID = (SELECT inserted.LossRegisteredUserID FROM inserted),
               DiaryDateTime = (SELECT inserted.DiaryDateTime FROM inserted)

          WHERE LossID = (SELECT inserted.LossID FROM inserted)
         
     END

0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 16359819
In order to update the text/ntext fields, you need to make use of either 'updatetext' or 'writetext' queries.Use UPDATETEXT to change only a portion of a text, ntext, or image column in place. Use WRITETEXT to update and replace an entire text, ntext, or image field.

In this case writetext is enoough

There is an example provided for writetext in bol

use pubs
Go
DECLARE @ptrval binary(16)
SELECT @ptrval = TEXTPTR(pr_info)
FROM pub_info pr, publishers p
WHERE p.pub_id = pr.pub_id
   AND p.pub_name = 'New Moon Books'
WRITETEXT pub_info.pr_info @ptrval 'New Moon Books (NMB) has just released another top ten publication. With the latest publication this makes NMB the hottest new publisher of the year!'
GO


Please go thru the above, once you unbderstand the concept, you can modify the 'ntext' field of your column.. In case of any doubts, pls contact
 
0
 

Author Comment

by:andichilds
ID: 16359875
Thank you very much angelIII, your solution worked.

Before I allocate the points, would the following code update my NText Field?

DELCARE @NTextValue BINARY(16)
SET @NTextValue = TEXTPTR((SELECT inserted.LossNotes FROM inserted))

UPDATE [LossSelectLoss]
          SET
               WRITETEXT LossNotes @NTextValue (SELECT inserted.LossNotes FROM inserted)

          WHERE LossID = (SELECT inserted.LossID FROM inserted)
0
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 16359917
no.

you have to copy the value in the ntext field in chunks, and even by row by looping on the length of the data and copying chunks of nvarchar(4000) bytes.
0
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 16360428
btw UPDATED(IncurredAmount)
only confirms that the named column was part of the data that may have been modified by the query
not that it actually has been modified... so


 INSERT INTO dbo.IncurredAmount
                    (
                         LossID,
                         RegisteredUserID,
                         IncurredAmount
                    )
                    SELECT inserted.LossID,2, inserted.IncurredAmount FROM inserted

should be

 INSERT INTO dbo.IncurredAmount
                    ( LossID, RegisteredUserID, IncurredAmount )
                    SELECT i.LossID,2, i.IncurredAmount
                      FROM inserted i
                      left outer join deleted as d
                      on i.lossid=d.lossid
                     where i.incurredamount <> d.incurredamount
                      and i.incurredamount is not null

and similar with the your update requirements...
0
 

Author Comment

by:andichilds
ID: 16360826
Thanks guys.

Still having problems with NText field. What is the maximum length for a VARCHAR field?

Thanks Lowfatspread, but Access only seems to send the updated data to SQL so I don't need to do this.
0
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 16363180
varchar is 8060  bytes in SQL 7 and SQL 2000
(Nvarchar is 4030 characters)

in SQL 2005 is 2GB but then you probably have to treat it as Text...

>>but Access only seems to send the updated data to SQL so I don't need to do this

your trigger is written in a style that suggests that you expect only 1 row at a time to  be processed by the trigger
thats not how sql works in general... you always need to write triggers on the basis that many rows may have
been affected by the source sql statements...

hth
0
 

Author Comment

by:andichilds
ID: 16368384
Do i have to remove the WHERE statement to make this trigger work for multiple rows?
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Suggested Courses

830 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