asked on

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

ASKER CERTIFIED SOLUTION

Guy Hengel [angelIII / a3]

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Aneesh

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

andichilds

ASKER

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]

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.

Lowfatspread

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

andichilds

ASKER

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.

Lowfatspread

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

andichilds

ASKER

Do i have to remove the WHERE statement to make this trigger work for multiple rows?