We help IT Professionals succeed at work.

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

andichilds
andichilds asked
on
Medium Priority
254 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

Comment
Watch Question

Billing Engineer
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2009
Commented:
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

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts
AneeshDatabase Consultant
CERTIFIED EXPERT
Top Expert 2009

Commented:
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
 

Author

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)
Guy Hengel [angelIII / a3]Billing Engineer
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2009

Commented:
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.
CERTIFIED EXPERT
Top Expert 2011

Commented:
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...

Author

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.
CERTIFIED EXPERT
Top Expert 2011

Commented:
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

Author

Commented:
Do i have to remove the WHERE statement to make this trigger work for multiple rows?
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.