Hi,

When a field in Table SAMPLE is updated (i.e. field 'Status' is updated to 'C'), I need to populate various fields in Table EXPORT.

I have written a Trigger based on Table SAMPLE, as follows:

CREATE TRIGGER [ExportSampleXML] ON [dbo].[SAMPLE]
AFTER UPDATE

AS

Declare @SampleNumber Integer
Declare @Status VarChar(1)

SELECT * FROM SAMPLE

    --Populate the Variables
    BEGIN
    SELECT @SAMPLENUMBER = SAMPLE_NUMBER FROM UPDATED
    WHERE STATUS = C

    EXEC usp_ExportXML @SAMPLENUMBER, @STATUS

END

Next, I have written the following Stored Procedure to UPDATE the Table EXPORT on the previous findings:

CREATE PROCEDURE [usp_ExportXML]

(
@SAMPLENUMBER [int],
@STATUS [varchar (1)]
)

As

INSERT into EXPORT ([SAMPLE_NUMBER],[STATUS])

    VALUES (@SAMPLENUMBER, @STATUS)

GO

The Table EXPORT is NOT being populated. Are my commands correct?

can anyone please help?

Thanks in advance,

Neal

A Northern Soul

__________________
Neal

A Northern Soul

Neal, you are doing this the hard way. You are close, but using the UPDATED system table the correct way you can forgo the SP.

Your trigger will look something like this:


CREATE TRIGGER [ExportSampleXML] ON [dbo].[SAMPLE]
AFTER UPDATE

AS

    INSERT into EXPORT ([SAMPLE_NUMBER],[STATUS]) SELECT SAMPLE_NUMBER, STATUS FROM UPDATED

END


Scott

Many Thanks,

Can I Delete all records from table EXPORT before I append the New record (in EXPORT), within the same trigger?

If so, how do I do this?

Neal

A Northern Soul

Sure, something like:

CREATE TRIGGER [ExportSampleXML] ON [dbo].[SAMPLE]
AFTER UPDATE

AS

    DELETE FROM EXPORT

    INSERT into EXPORT ([SAMPLE_NUMBER],[STATUS]) SELECT SAMPLE_NUMBER, STATUS FROM UPDATED

END

Hi,

Can anyone please help with the following?

CREATE TRIGGER [ExportSampleXML] ON [dbo].[SAMPLE]
AFTER UPDATE

AS

BEGIN
    --DELETE FROM EXPORT
     INSERT into [dbo].[EXPORT] ([SAMPLE_NUMBER],[STATUS]) SELECT SAMPLE_NUMBER, STATUS FROM UPDATED
        WHERE STATUS = 'C'
END

When UPDATING a record using the aforementioned trigger, the following SQL error message is displayed:

[MicroSoft][ODBC SQL Server Driver][SQL Server]Invalid Object name 'UPDATED'.

I am using SQL Server Db 2000, is this a problem with early versions of SQL Server?

Neal

A Northern Soul

There is no 'UPDATED' logical table in the context of a trigger. There are only the 'deleted' and 'inserted' tables.

In the context of a trigger, an update to the underlying table is considered a delete of the original row followed by an insert of the new, updated row.

It's better this way, since the combination of both tables gives you access to the data both before and after the update. Note that for a delete or insert operation, one or the other of these tables has no rows in it.

Jeff Mason
Custom Apps, Inc.
www.custom-apps.com

Hi,

Can anyone please help?

If the field STATUS on table(SAMPLE) is UPDATED to Value 'C', I need to clear table(EXPORT) and then populate fields within table(EXPORT) with various values held on the current SAMPLE record.

I am having trouble executing the correct results from the following trigger. What is the correct method?

CREATE TRIGGER [ExportSampleXML] ON [dbo].[SAMPLE]
FOR INSERT

AS

BEGIN
    IF UPDATE(STATUS)
    DELETE FROM EXPORT
    INSERT into [dbo].[EXPORT] ([SAMPLE_NUMBER],[STATUS]) SELECT SAMPLE_NUMBER, STATUS FROM INSERTED
        WHERE STATUS = 'C'
END

It appears that the trigger is trying to update all rows with a value of 'C' (Message = 'Too many records will be affected....').

Thanks in advance,

Neal

A Northern Soul