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

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

andichildsAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Guy Hengel [angelIII / a3]Billing EngineerCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Aneesh RetnakaranDatabase AdministratorCommented:
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
andichildsAuthor Commented:
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
LowfatspreadCommented:
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
andichildsAuthor Commented:
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
LowfatspreadCommented:
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
andichildsAuthor Commented:
Do i have to remove the WHERE statement to make this trigger work for multiple rows?
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.