How do I know which record is being updated in Update trigger?

Hi All,
Thanks in advance!
I want to generate an XML file in a Update trigger script.
Only the updated record needs to be exported to the XML file.
How do I know which record is being updated in the Update trigger?
Thank you so much!
Regards,
SeanSean,
Could be more than one record. Use the DELETED and INSERTED pseudo/logical
tables available within the firing of the trigger.
HTH
Jerry
"Sean" <ventilla@.hotmail.com> wrote in message
news:eZpeMm5zFHA.1924@.TK2MSFTNGP14.phx.gbl...
> Hi All,
> Thanks in advance!
> I want to generate an XML file in a Update trigger script.
> Only the updated record needs to be exported to the XML file.
> How do I know which record is being updated in the Update trigger?
> Thank you so much!
>
> Regards,
> Sean
>|||Define being updated. If you mean the rows that were touched, even if all
values remain the same:
select *
from inserted
If you mean those that have actually changed:
select *
from inserted
join deleted
on <some unchangable value, like a surrogate (identity)
key>
where inserted.col1 <> deleted.col2 --not nullable
or (inserted.col2 <> deleted.col2
or (inserted.col2 is null and deleted.col2 is not null)
or (inserted.col2 is not null and deleted.col2 is null) )
If you mean something else, then please provide more info.
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Arguments are to be avoided: they are always vulgar and often convincing."
(Oscar Wilde)
"Sean" <ventilla@.hotmail.com> wrote in message
news:eZpeMm5zFHA.1924@.TK2MSFTNGP14.phx.gbl...
> Hi All,
> Thanks in advance!
> I want to generate an XML file in a Update trigger script.
> Only the updated record needs to be exported to the XML file.
> How do I know which record is being updated in the Update trigger?
> Thank you so much!
>
> Regards,
> Sean
>|||Dear Louis,
I need all the records that have changed and inserted.
That means a modified record and new record.
For example:
CREATE TRIGGER trgUpdate
ON User_Master
FOR UPDATE
AS
DECLARE @.FileName varchar(50),
@.bcpCommand varchar(2000)
SET @.FileName =
REPLACE('c:\result_'+CONVERT(char(8),GET
DATE(),1)+'.xml','/','-')
SET @.bcpCommand = 'bcp "SELECT * FROM KDMNN..User_Master Where UserID=' +
CONVERT(varchar(5),@.@.identity) + ' for xml raw" queryout "'
SET @.bcpCommand = @.bcpCommand + @.FileName + '" -U sa -P -c'
EXEC master..xp_cmdshell @.bcpCommand
I have a problem detecting the @.@.identity. I could not get the information
of which records have been modified and inserted.
Thank you very much!
Regards,
Sean
"Louis Davidson" <dr_dontspamme_sql@.hotmail.com> wrote in message
news:uCMwbb7zFHA.2540@.TK2MSFTNGP09.phx.gbl...
> Define being updated. If you mean the rows that were touched, even if all
> values remain the same:
> select *
> from inserted
> If you mean those that have actually changed:
> select *
> from inserted
> join deleted
> on <some unchangable value, like a surrogate (identity)
> key>
> where inserted.col1 <> deleted.col2 --not nullable
> or (inserted.col2 <> deleted.col2
> or (inserted.col2 is null and deleted.col2 is not null)
> or (inserted.col2 is not null and deleted.col2 is null) )
> If you mean something else, then please provide more info.
> --
> ----
--
> Louis Davidson - http://spaces.msn.com/members/drsql/
> SQL Server MVP
> "Arguments are to be avoided: they are always vulgar and often
> convincing." (Oscar Wilde)
> "Sean" <ventilla@.hotmail.com> wrote in message
> news:eZpeMm5zFHA.1924@.TK2MSFTNGP14.phx.gbl...
>|||Yeah, you cannot rely on identities to identify a row like this. You really
need to have another key that you can expect not to change. Since you
insert the values, you know them and can trust them.
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Arguments are to be avoided: they are always vulgar and often convincing."
(Oscar Wilde)
"Sean" <ventilla@.hotmail.com> wrote in message
news:uTlSTNG0FHA.908@.tk2msftngp13.phx.gbl...
> Dear Louis,
> I need all the records that have changed and inserted.
> That means a modified record and new record.
> For example:
> CREATE TRIGGER trgUpdate
> ON User_Master
> FOR UPDATE
> AS
> DECLARE @.FileName varchar(50),
> @.bcpCommand varchar(2000)
> SET @.FileName =
> REPLACE('c:\result_'+CONVERT(char(8),GET
DATE(),1)+'.xml','/','-')
> SET @.bcpCommand = 'bcp "SELECT * FROM KDMNN..User_Master Where UserID=' +
> CONVERT(varchar(5),@.@.identity) + ' for xml raw" queryout "'
> SET @.bcpCommand = @.bcpCommand + @.FileName + '" -U sa -P -c'
> EXEC master..xp_cmdshell @.bcpCommand
> I have a problem detecting the @.@.identity. I could not get the information
> of which records have been modified and inserted.
> Thank you very much!
>
> Regards,
> Sean
> "Louis Davidson" <dr_dontspamme_sql@.hotmail.com> wrote in message
> news:uCMwbb7zFHA.2540@.TK2MSFTNGP09.phx.gbl...
>|||Dear Louis,
Thanks for you information.
It helps a lot!
I am trying to copy the modified or new data from User_Master into a
User_MasterTemp table using an Update trigger.
CREATE TRIGGER trgUpdate ON User_Master
FOR UPDATE
AS
Delete From User_MasterTemp
Insert into User_MasterTemp Select * From Inserted
GO
Then do a exporting to XML file from the temp table using an Insert trigger.
CREATE TRIGGER User_MasterInsert ON [dbo].[User_MasterTemp]
FOR INSERT
AS
DECLARE @.FileName varchar(50),
@.bcpCommand varchar(2000)
SET @.FileName =
REPLACE('c:\result_'+CONVERT(char(8),GET
DATE(),1)+'.xml','/','-')
SET @.bcpCommand = 'bcp "SELECT * FROM KDMNN..User_MasterTemp for xml raw"
queryout "'
SET @.bcpCommand = @.bcpCommand + @.FileName + '" -U sa -P -c'
EXEC master..xp_cmdshell @.bcpCommand
Hopefully, the code can work. I am doing the troubleshooting cause the
script in the Insert trigger hangs the SQL Server.
Regards,
Sean
"Louis Davidson" <dr_dontspamme_sql@.hotmail.com> wrote in message
news:O04yriG0FHA.916@.TK2MSFTNGP10.phx.gbl...
> Yeah, you cannot rely on identities to identify a row like this. You
> really need to have another key that you can expect not to change. Since
> you insert the values, you know them and can trust them.
> --
> ----
--
> Louis Davidson - http://spaces.msn.com/members/drsql/
> SQL Server MVP
> "Arguments are to be avoided: they are always vulgar and often
> convincing." (Oscar Wilde)
> "Sean" <ventilla@.hotmail.com> wrote in message
> news:uTlSTNG0FHA.908@.tk2msftngp13.phx.gbl...
>

How do I know what action triggered a trigger...

Hello

If I have a trigger for INSERT and DELETE, how do I know what action was executed ??
I prefer not to split the trigger to one for each action, because the the procedure for both is almost the same.

Another question, How can I know what records were updated? I need to concatenate the ID/ID's to the SQL in the procedure.

Thanks,

Inon.If (EXISTS(SELECT * FROM INSERTED))
Trigger for INSERT
If (EXISTS(SELECT * FROM DELETED))
Trigger for DELETE

go thru BOL for more information about INSERTED,DELETED tables|||If (EXISTS(SELECT * FROM INSERTED))
Trigger for INSERT
If (EXISTS(SELECT * FROM DELETED))
Trigger for DELETE

go thru BOL for more information about INSERTED,DELETED tables

Ok Thanks,
But tell me, isn't it a bit spendy on resources ?

Inon.

how do I get the name of the table that fired a trigger?

I have a trigger set for INSERT conditions on a table and I want to use the table name in an argument within the trigger. Does anyone know of a way of obtaining that tablename that fired the trigger without having to hardcode it? (I want to be able to re-use the trigger code on various tables without having to alter it for each table)

Thanks

Colin

Here it is,

Code Snippet

Create table Main(i int)

Go

Create trigger main_trg on Main for insert

as

Declare @.TableName NVarchar(300)

Select

@.TableName = object_Name(parent_obj)

from

sysobjects where id=@.@.PROCID

Select @.TableName

Go

Insert Into main values(1)

|||Perfect - works like a charm.
2 questions though -
1. is there any significance to you using NVarchar(300) as the datatype?
2. Do you know the syntax needed to be able to use a variable like @.TableName in a select statement instead of hardcoding the table name?

Thanks again for your help.

Colin|||

1. Is there any significance to you using NVarchar(300) as the datatype?

Since the sql server objects are Unicode values, it always better to use the NVarchar datatype. Regarding Length I recommend to use 128. (sysname = Nvarchar(128)

2. Do you know the syntax needed to be able to use a variable like @.TableName in a select statement instead of hard coding the

You have to use the dynamic sql here,

Code Snippet

Exec(N'Select * From ' + @.TableName);

--or

Declare @.SQL as Nvarchar(4000);

Set @.SQL = N'Select * From ' + @.TableName

Exec sp_executesql @.SQL

|||OK, one last question!
What's the significance of the 'N' in EXEC(N'Select...... ?

Thanks|||

I think N come from Unicode

|||

Yes, exactly the regular ascii string will be enclosed in the single quote ‘.

To identify the Unicode values databases uses the N prefix.

How do I get the ID of the last entry?

Hi,
I need to know the ID of the last entry so that I can use it in a trigger.
How do I get the ID of the last INSERT'ed record? Do I use Scope_Identity()
for that?
Thanks,
Sam> I need to know the ID of the last entry so that I can use it in a trigger.
What exactly are you doing in the trigger? Is it prepared for multiple-row
inserts (e.g. BULK INSERT or INSERT ... SELECT ...FROM)?
For a single insert within a stored procedure, you would use
SCOPE_IDENTITY(), but since you can't be sure that an insert is only one
row, the trigger can't say something like SCOPE_ALL_IDENTITIES(). If you
explain exactly what you are trying to do, we can help you with code that
uses the values from the inserted pseudo-table within the trigger (or moving
your logic outside of the trigger).
A|||Sam wrote:
> Hi,
> I need to know the ID of the last entry so that I can use it in a trigger.
> How do I get the ID of the last INSERT'ed record? Do I use Scope_Identity(
)
> for that?
> --
> Thanks,
> Sam
SCOPE_IDENTITY() will give you the IDENTITY value of the last inserted
row in a proc. In a trigger however you should use the INSERTED virtual
table instead. A well-behaved trigger needs to accommodate multiple row
updates so returning a single last inserted value is not sufficient.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||The user is actually using an Access front-end to make enteries and that's
one record at a time. I also have a web interface to the app. That uses a
stored procedure to make entries into the table. That again will be one
record at a time.
Having said this I assume, SCOPE_IDENTITY() should do the job. Correct?
--
Thanks,
Sam
"Aaron Bertrand [SQL Server MVP]" wrote:

> What exactly are you doing in the trigger? Is it prepared for multiple-ro
w
> inserts (e.g. BULK INSERT or INSERT ... SELECT ...FROM)?
> For a single insert within a stored procedure, you would use
> SCOPE_IDENTITY(), but since you can't be sure that an insert is only one
> row, the trigger can't say something like SCOPE_ALL_IDENTITIES(). If you
> explain exactly what you are trying to do, we can help you with code that
> uses the values from the inserted pseudo-table within the trigger (or movi
ng
> your logic outside of the trigger).
> A
>
>|||Sam wrote:
> The user is actually using an Access front-end to make enteries and that's
> one record at a time. I also have a web interface to the app. That uses a
> stored procedure to make entries into the table. That again will be one
> record at a time.
> Having said this I assume, SCOPE_IDENTITY() should do the job. Correct?
> --
> Thanks,
>
SCOPE_IDENTITY() in a trigger will not return the value from the insert
that fired the trigger. Even if it did and even if what you said is
true, a trigger that can only support single row inserts is an accident
waiting to happen. Use the Inserted table and write set-based code so
that the trigger will be safe. Otherwise whoever, supports and
maintains the system will curse you one day.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||David,
Thank you for setting me straight. I guess I resisted using the INSERTED
virtual table because I've never used it before. I'm looking at BOL but can
you suggest any other resource on the web that can get me started? Thanks
again.
Thanks,
Sam
"David Portas" wrote:

> Sam wrote:
> SCOPE_IDENTITY() in a trigger will not return the value from the insert
> that fired the trigger. Even if it did and even if what you said is
> true, a trigger that can only support single row inserts is an accident
> waiting to happen. Use the Inserted table and write set-based code so
> that the trigger will be safe. Otherwise whoever, supports and
> maintains the system will curse you one day.
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>|||David Portas wrote:
> SCOPE_IDENTITY() in a trigger will not return the value from the insert
> that fired the trigger.
BTW, @.@.IDENTITY will. I say that for the sake of completeness not
because I think @.@.IDENTITY is the best solution.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||David,
Just so I'm clear, should I use @.@.IDENTITY as opposed to INSERTED table? Is
that the best solution?
Thanks,
Sam
"David Portas" wrote:

> David Portas wrote:
> BTW, @.@.IDENTITY will. I say that for the sake of completeness not
> because I think @.@.IDENTITY is the best solution.
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>|||Well, like I said before,
"If you explain exactly what you are trying to do, we can help you with code
that
uses the values from the inserted pseudo-table within the trigger (or moving
your logic outside of the trigger)."
"Sam" <Sam@.discussions.microsoft.com> wrote in message
news:332CD2E2-C122-4786-8728-60B8562CFE23@.microsoft.com...
> David,
> Thank you for setting me straight. I guess I resisted using the INSERTED
> virtual table because I've never used it before. I'm looking at BOL but
> can
> you suggest any other resource on the web that can get me started? Thanks
> again.|||Well, like I said before,
"If you explain exactly what you are trying to do, we can help you with code
that
uses the values from the inserted pseudo-table within the trigger (or moving
your logic outside of the trigger)."
"Sam" <Sam@.discussions.microsoft.com> wrote in message
news:332CD2E2-C122-4786-8728-60B8562CFE23@.microsoft.com...
> David,
> Thank you for setting me straight. I guess I resisted using the INSERTED
> virtual table because I've never used it before. I'm looking at BOL but
> can
> you suggest any other resource on the web that can get me started? Thanks
> again.

how do I get a trigger just to copy the inserted row.

HI I have a trigger which I want to copy any rows in the MSmerge_history into a archieve table. I am using this trigger:

CREATE TRIGGER History_replication ON
[dbo].[MSmerge_history]
FOR INSERT
AS
INSERT [dbo].[MSmerge_history_archive]
(
agent_id,
runstatus,
start_time,
[time],
duration,
comments,
delivery_time,
delivery_rate,
publisher_insertcount,
publisher_updatecount,
publisher_deletecount,
publisher_conflictcount,
subscriber_insertcount,
subscriber_updatecount,
subscriber_deletecount,
subscriber_conflictcount,
error_id,
[timestamp] ,
updateable_row
)
SELECT
agent_id,
runstatus,
start_time,
[time] ,
duration,
comments,
delivery_time,
delivery_rate,
publisher_insertcount,
publisher_updatecount,
publisher_deletecount,
publisher_conflictcount,
subscriber_insertcount,
subscriber_updatecount,
subscriber_deletecount,
subscriber_conflictcount,
error_id,
[timestamp],

How this copy the entire contence of the table into the archive each time someone insert a row. How do I get it to only insert the row which had triggered the insert? EdCREATE TRIGGER History_replication ON
[dbo].[MSmerge_history]
FOR INSERT
AS
INSERT [dbo].[MSmerge_history_archive]
(
agent_id,
runstatus,
start_time,
[time],
duration,
comments,
delivery_time,
delivery_rate,
publisher_insertcount,
publisher_updatecount,
publisher_deletecount,
publisher_conflictcount,
subscriber_insertcount,
subscriber_updatecount,
subscriber_deletecount,
subscriber_conflictcount,
error_id,
[timestamp] ,
updateable_row
)
SELECT
agent_id,
runstatus,
start_time,
[time] ,
duration,
comments,
delivery_time,
delivery_rate,
publisher_insertcount,
publisher_updatecount,
publisher_deletecount,
publisher_conflictcount,
subscriber_insertcount,
subscriber_updatecount,
subscriber_deletecount,
subscriber_conflictcount,
error_id,
[timestamp]
FROM inserted

Inserted is virtual table used by triggers.|||Excellent thanks I've got that working now, Ed