[RESOLVED] Triggers and Identity Values

[uk_codemonkey]

Hi all

I'm currently trying to write a trigger which will see when a field in a particular table has been updated and will create a new record in a completely separate table. So far I've set it up so that when the trigger executes, if the desired field has been updated, it will fire a stored procedure which will do everything else that I need it to do.

Everything's going fine in terms of writing the trigger and the procedure. However, I want to set the trigger up so that when it executes the stored procedure, that procedure only affects the record where the desired column was updated. How do I get SQL to identify the identity value of the record that caused the trigger to be fired? I did think of using @@Identity but that will only give me the identity value of the LAST record created, as opposed to the identity value of the last record where that column was updated.

Thanks in advance.


Ian

[szlamany]

Please show the code for the trigger please.

[uk_codemonkey]

Hello

The code that I wrote for the trigger is this:

Create Trigger dbo.trg_CallValueAnalysis

On

dbo.Caller_Profile

After Update

As

If (Update (Completed_Date))
Begin

Exec <<stored procedure name>>

End

Go

In case this isn't clear, Caller_Profile is the table that the trigger will be associated with. The idea is that the trigger would execute if the Completed_Date field has been changed - the only change that would happen here is that a timestamp would be inserted into a NULL field. Ideally the stored procedure that gets called would be receiving the identity value of this table so that it knows which record it's working with.

HTH

TIA

Ian

[szlamany]

Ok - so this only happens to existing records - since it's based on a change to a field - right?

There are two "pseudo" tables available in a trigger - INSERTED and DELETED.

The INSERTED table contains the data that was just INSERTED or UPDATED - so it's what you want to use.

Something like:

Exec <<stored procedure name>> Inserted.IdentField

Here I am passing the IdentField (replace this bogus field name with the real name of the identify field) of the INSERTED table as a parameter to the SPROC.

Does this make sense?

[uk_codemonkey]

Just to be clear, would I be correct in saying that I want to execute the stored procedure in the following manner:

exec sprocname Caller_Profile.Profile_ID

In this example, Caller_Profile is the name of the table that's being updated, and Profile_ID is the identity column within that table.

TIA

Ian

[szlamany]

Almost...

What you want to say is

exec sprocname Inserted.Profile_ID

Inserted is a pseudo table that TRIGGERS have access to. It contains the single row of the "data" that was just inserted or updated. The "columns" exactly match the real table - in this case Caller_Profile.

The reason you cannot use CALLER_PROFILE is because there is a "before" and "after" version of the row - the before version is in DELETED and the after version is in INSERTED.

[uk_codemonkey]

Nice one. I'll give that lot a try and let you know the outcome.

Thanks

[uk_codemonkey]

Hello

I've just tried that, using the following syntax

Exec ESS_Reporting_CALM.dbo.spih_CALM_IbTele_CallValueAnalysis Inserted.Profile_ID

When I try to save the trigger, SQL Server throws an issue with the period between Inserted and Profile_ID.

Am I doing something wrong?

Ian

[szlamany]

Just to test the syntax change

Exec ESS_Reporting_CALM.dbo.spih_CALM_IbTele_CallValueAnalysis Inserted.Profile_ID

to

Exec ESS_Reporting_CALM.dbo.spih_CALM_IbTele_CallValueAnalysis 1

Where 1 is simply a hardwired value.

Then change

Exec ESS_Reporting_CALM.dbo.spih_CALM_IbTele_CallValueAnalysis Inserted.Profile_ID

To

Select Inserted.Profile_Id

Let's see what half of that EXEC statement is dying...

[uk_codemonkey]

Hello

It appears to be an issue with the Select.Profile_ID portion of the statement - if I get the trigger to fire the stored procedure with a hard-wired value in it, it accepts it without complaint. However, I get the following error when I try to parse in Select.Profile_ID

"The multi-part identifier "Inserted.Profile_ID" could not be bound."

Thanks

Ian

[szlamany]

The table dbo.Caller_Profile really does have a field called PROFILE_ID?

I don't use triggers - so this is all from just what I've learned during research on MS SQL.

It doesn't make any sense that you cannot refer to a field like that.

[techgnome]

Actually, it does make sense, once you get a grasp on the scope of the objects being manipulated.... you can't pass the ID directly from Inserted... you need to extract it to a varaable FIRST, then you can pass it. Secondly, it's a dangerous thing to be accessing the table from an SP called by a trigger.... the reason: the table is in a transitional state, the record neither exists, nor does it not exist. In addition the table has a lock on it, potentially making it unavailable for the SP to read it. Your best bet would be to extract the data out of Inserted and pass it to the SP, so that it doesn't need to access the original source table in the first place.

FYI:
Select Inserted.Profile_Id
failed because a Trigger by definition cannot return data... the "Select Inserted.Profile_Id" would ONLY work if it was then assigned to a variable (not to mention it's an incomplete sql query as well):
Select @NewID = Inserted.Profile_Id FROM Inserted


Once you have @NewID, you can pass it to your SP:
Exec ESS_Reporting_CALM.dbo.spih_CALM_IbTele_CallValueAnalysis @NewID

-- It's just occurred to me, that Inserted, while contains only a single row, it is a table

Inserted is a pseudo table

... that's why Inserted.ProfileID must first be extracted before being sent to the SP... you can't run an SP as part of a select... unless it's a scalar or in-line function....

Is any of this making sense? IE - you can't pass a value of a field in a pseudo table any more than you can pass the field of a real table to an SP.

-tg

[szlamany]

@tg - oops - I missed the obvious - need to extract to a variable. I don't do triggers...

...so I was out of my scope

If you are going to extract into a variable make sure to put SET NOCOUNT ON - to avoid recordset returns from the trigger (I read that today )...

@uk - Why do you need to call a SPROC - why not put the body of the SPROC logic in that trigger anyway??

[uk_codemonkey]

Thanks very much to both Techgnome and Szlamany - I have managed to get this working by declaring a variable in the trigger, packing like you suggested and then using that variable in the exec statement for the sproc, so I am now at the stage where the sproc fires and creates a record in a table corresponding to the record being updated in the table that fires the trigger.

Szlamany, to answer your question, I'm working with two different databases here - the main database which the end-users will access (and which fires the trigger) and the reporting database that will contain all of the tables and sprocs that I will use with SQL Server Reporting Services (just to name-drop for a second). The reason that I wanted to get this working is because we are going to be providing a report to our Client which analyses the value of calls taken for this particular service. My master plan is to set it up so that the analysis code is in the reporting database and can be amended by me at any time without actually having to update the trigger tables in the end-user database.

I think we're done here people. As ever, VBForums has come up trumps!!!