I need to implement my cascading deletes on a SQL database. Is it better (performance/reliablility-wise) to use the Foreign Key Cascading Deletes or to just write my own triggers to do the deletes?
I was hoping someone had experimented and found which works best.DRI(declarative referential integrity) triggers are used when Cascade Deletes and Updates cannot be implemented in the RDBMS(relational database management system) as in SQL Server 7.0 and below. In SQL Server 2000 using Cascade Delete and Cascade Update is the way to go. The reason is reliability related because DRI(declarative referential integrity) with Cascade Delete and Cascade Update is guaranteed.
On a side note if you have a why question run a search for Peter Chen 1976 ERD paper on Google and see the 26 pages paper in Algebra that created DRI(declarative referential integrity) in ANSI SQL. Hope this helps.|||Thanks for the great info. All my servers run SQL 2000 so we'll be going with the built-in cascading deletes.
Showing posts with label trigger. Show all posts
Showing posts with label trigger. Show all posts
Sunday, March 11, 2012
Thursday, March 8, 2012
Cascade updates
I ran a bulk update on a SQL database for a range between dateswhere there
were trigger and possibly foreign keys. the parent updated fine but it
changed multiple child records although they were not relatedHi
Can you show us DDL + sample data + your query?
"Dangerfield" <Dangerfield@.discussions.microsoft.com> wrote in message
news:2E57BC94-A3B0-46CA-9349-C2DEB7C79F46@.microsoft.com...
> I ran a bulk update on a SQL database for a range between dateswhere there
> were trigger and possibly foreign keys. the parent updated fine but it
> changed multiple child records although they were not related
were trigger and possibly foreign keys. the parent updated fine but it
changed multiple child records although they were not relatedHi
Can you show us DDL + sample data + your query?
"Dangerfield" <Dangerfield@.discussions.microsoft.com> wrote in message
news:2E57BC94-A3B0-46CA-9349-C2DEB7C79F46@.microsoft.com...
> I ran a bulk update on a SQL database for a range between dateswhere there
> were trigger and possibly foreign keys. the parent updated fine but it
> changed multiple child records although they were not related
Cascade Delete
Hi,
I need to delete data from child table when I delete data from parent
table. I think trigger can be used in this scenario.
I want to know your views on using trigger for this purpose.
And can someone give me a sample on how to do it using triggers.
Thanks
Kiranor define your foreign key to cascade on delete. No trigger required
"Kiran" <kiran@.nospam.net> wrote in message
news:ussCTgMqFHA.3520@.tk2msftngp13.phx.gbl...
> Hi,
> I need to delete data from child table when I delete data from parent
> table. I think trigger can be used in this scenario.
> I want to know your views on using trigger for this purpose.
> And can someone give me a sample on how to do it using triggers.
> Thanks
> Kiran|||You can "automate" deletes using ON DELETE CASCADE when creating/altering
foreign key constraints.
Read more here:
http://msdn.microsoft.com/library/d...br />
3ied.asp
Example:
alter table owner.foreignkey_table
add constraint fk_name
foreign key (foreignkey_column_name)
references owner.primarykey_table
(primarykey_column_name)
on delete cascade
go
ML|||You can accomplish this in SQL Server 2000 without a trigger, using ON
DELETE CASCADE when creating FOREIGN KEY constraints.
The following script illustrates this:
use tempdb
go
set nocount on
go
create table tbl1 (id int not null primary key)
go
insert tbl1 values (1)
insert tbl1 values (2)
go
create table tbl2
(
id int not null primary key
references tbl1 (id) on delete cascade
)
go
insert tbl2 values (1)
insert tbl2 values (2)
go
delete from tbl1
where id = 1
go
select *
from tbl2
go
drop table tbl2
drop table tbl1
go
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--
"Kiran" <kiran@.nospam.net> wrote in message
news:ussCTgMqFHA.3520@.tk2msftngp13.phx.gbl...
> Hi,
> I need to delete data from child table when I delete data from parent
> table. I think trigger can be used in this scenario.
> I want to know your views on using trigger for this purpose.
> And can someone give me a sample on how to do it using triggers.
> Thanks
> Kiran|||ML wrote:
> You can "automate" deletes using ON DELETE CASCADE when creating/altering
> foreign key constraints.
> Read more here:
> http://msdn.microsoft.com/library/d... />
z_3ied.asp
> Example:
> alter table owner.foreignkey_table
> add constraint fk_name
> foreign key (foreignkey_column_name)
> references owner.primarykey_table
> (primarykey_column_name)
> on delete cascade
> go
>
> ML
Thanks ML|||Adam Machanic wrote:
> You can accomplish this in SQL Server 2000 without a trigger, using ON
> DELETE CASCADE when creating FOREIGN KEY constraints.
> The following script illustrates this:
>
> use tempdb
> go
> set nocount on
> go
> create table tbl1 (id int not null primary key)
> go
> insert tbl1 values (1)
> insert tbl1 values (2)
> go
> create table tbl2
> (
> id int not null primary key
> references tbl1 (id) on delete cascade
> )
> go
> insert tbl2 values (1)
> insert tbl2 values (2)
> go
> delete from tbl1
> where id = 1
> go
> select *
> from tbl2
> go
> drop table tbl2
> drop table tbl1
> go
>
>
Thanks Adam|||Farmer wrote:
> or define your foreign key to cascade on delete. No trigger required
>
> "Kiran" <kiran@.nospam.net> wrote in message
> news:ussCTgMqFHA.3520@.tk2msftngp13.phx.gbl...
>
>
>
thanks Farmer
I need to delete data from child table when I delete data from parent
table. I think trigger can be used in this scenario.
I want to know your views on using trigger for this purpose.
And can someone give me a sample on how to do it using triggers.
Thanks
Kiranor define your foreign key to cascade on delete. No trigger required
"Kiran" <kiran@.nospam.net> wrote in message
news:ussCTgMqFHA.3520@.tk2msftngp13.phx.gbl...
> Hi,
> I need to delete data from child table when I delete data from parent
> table. I think trigger can be used in this scenario.
> I want to know your views on using trigger for this purpose.
> And can someone give me a sample on how to do it using triggers.
> Thanks
> Kiran|||You can "automate" deletes using ON DELETE CASCADE when creating/altering
foreign key constraints.
Read more here:
http://msdn.microsoft.com/library/d...br />
3ied.asp
Example:
alter table owner.foreignkey_table
add constraint fk_name
foreign key (foreignkey_column_name)
references owner.primarykey_table
(primarykey_column_name)
on delete cascade
go
ML|||You can accomplish this in SQL Server 2000 without a trigger, using ON
DELETE CASCADE when creating FOREIGN KEY constraints.
The following script illustrates this:
use tempdb
go
set nocount on
go
create table tbl1 (id int not null primary key)
go
insert tbl1 values (1)
insert tbl1 values (2)
go
create table tbl2
(
id int not null primary key
references tbl1 (id) on delete cascade
)
go
insert tbl2 values (1)
insert tbl2 values (2)
go
delete from tbl1
where id = 1
go
select *
from tbl2
go
drop table tbl2
drop table tbl1
go
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--
"Kiran" <kiran@.nospam.net> wrote in message
news:ussCTgMqFHA.3520@.tk2msftngp13.phx.gbl...
> Hi,
> I need to delete data from child table when I delete data from parent
> table. I think trigger can be used in this scenario.
> I want to know your views on using trigger for this purpose.
> And can someone give me a sample on how to do it using triggers.
> Thanks
> Kiran|||ML wrote:
> You can "automate" deletes using ON DELETE CASCADE when creating/altering
> foreign key constraints.
> Read more here:
> http://msdn.microsoft.com/library/d... />
z_3ied.asp
> Example:
> alter table owner.foreignkey_table
> add constraint fk_name
> foreign key (foreignkey_column_name)
> references owner.primarykey_table
> (primarykey_column_name)
> on delete cascade
> go
>
> ML
Thanks ML|||Adam Machanic wrote:
> You can accomplish this in SQL Server 2000 without a trigger, using ON
> DELETE CASCADE when creating FOREIGN KEY constraints.
> The following script illustrates this:
>
> use tempdb
> go
> set nocount on
> go
> create table tbl1 (id int not null primary key)
> go
> insert tbl1 values (1)
> insert tbl1 values (2)
> go
> create table tbl2
> (
> id int not null primary key
> references tbl1 (id) on delete cascade
> )
> go
> insert tbl2 values (1)
> insert tbl2 values (2)
> go
> delete from tbl1
> where id = 1
> go
> select *
> from tbl2
> go
> drop table tbl2
> drop table tbl1
> go
>
>
Thanks Adam|||Farmer wrote:
> or define your foreign key to cascade on delete. No trigger required
>
> "Kiran" <kiran@.nospam.net> wrote in message
> news:ussCTgMqFHA.3520@.tk2msftngp13.phx.gbl...
>
>
>
thanks Farmer
Sunday, February 19, 2012
Capture MAC address in trigger
I have a simple trigger that writes out records to another table when a user
deletes a record. I am looking for a way to capture the Network Address valu
e
that is associated with the process. Is that possible ?
Here is the trigger. I want to insert the Network Address value into the
Lastuser coulumn in the Contact1del table
CREATE TRIGGER C1Delete ON Contact1
FOR DELETE
AS
insert into Contact1DEL (ACCOUNTNO, COMPANY, CONTACT, LASTNAME, DEPARTMENT,
TITLE, SECR, PHONE1, PHONE2, PHONE3, FAX, EXT1, EXT2, EXT3, EXT4, ADDRESS1,
ADDRESS2, ADDRESS3, CITY, STATE, ZIP, COUNTRY, DEAR, SOURCE, KEY1, KEY2,
KEY3, KEY4, KEY5, STATUS, MERGECODES, CREATEBY,
CREATEON, CREATEAT, OWNER, LASTUSER, LASTDATE, LASTTIME, RECID)
select ACCOUNTNO, COMPANY, CONTACT, LASTNAME, DEPARTMENT, TITLE, SECR,
PHONE1, PHONE2, PHONE3, FAX, EXT1, EXT2, EXT3, EXT4, ADDRESS1,
ADDRESS2, ADDRESS3, CITY, STATE, ZIP, COUNTRY, DEAR, SOURCE, KEY1, KEY2,
KEY3, KEY4, KEY5, STATUS, MERGECODES, CREATEBY,
CREATEON, CREATEAT, OWNER, LASTUSER, GETDATE(), LASTTIME, RECID
from deleted
ThanksHOST_NAME() maybe?|||jenks wrote:
> I have a simple trigger that writes out records to another table when
> a user deletes a record. I am looking for a way to capture the
> Network Address value that is associated with the process. Is that
> possible ?
> Here is the trigger. I want to insert the Network Address value into
> the Lastuser coulumn in the Contact1del table
> CREATE TRIGGER C1Delete ON Contact1
> FOR DELETE
> AS
>
> insert into Contact1DEL (ACCOUNTNO, COMPANY, CONTACT, LASTNAME,
> DEPARTMENT, TITLE, SECR, PHONE1, PHONE2, PHONE3, FAX, EXT1, EXT2,
> EXT3, EXT4, ADDRESS1, ADDRESS2, ADDRESS3, CITY, STATE, ZIP, COUNTRY,
> DEAR, SOURCE, KEY1, KEY2, KEY3, KEY4, KEY5, STATUS, MERGECODES,
> CREATEBY,
> CREATEON, CREATEAT, OWNER, LASTUSER, LASTDATE, LASTTIME, RECID)
> select ACCOUNTNO, COMPANY, CONTACT, LASTNAME, DEPARTMENT, TITLE, SECR,
> PHONE1, PHONE2, PHONE3, FAX, EXT1, EXT2, EXT3, EXT4, ADDRESS1,
> ADDRESS2, ADDRESS3, CITY, STATE, ZIP, COUNTRY, DEAR, SOURCE, KEY1,
> KEY2, KEY3, KEY4, KEY5, STATUS, MERGECODES, CREATEBY,
> CREATEON, CREATEAT, OWNER, LASTUSER, GETDATE(), LASTTIME, RECID
> from deleted
> Thanks
Select
net_address
From
master..sysprocesses
Where
spid = @.@.spid
This will give you the mac address for the current spid. In your case,
you could use an embedded SELECT or grab the mac address before the
insert. For example:
Insert into #ttt (col1, col2)
Select 1, (
Select
net_address
From
master..sysprocesses
Where
spid = @.@.spid )
David Gugick - SQL Server MVP
Quest Software|||this might help:
select net_address from master..sysprocesses where spid=@.@.spid
dean
"jenks" <jenks@.discussions.microsoft.com> wrote in message
news:8B260AE5-DAF4-43CD-8A29-9232573A39EE@.microsoft.com...
>I have a simple trigger that writes out records to another table when a
>user
> deletes a record. I am looking for a way to capture the Network Address
> value
> that is associated with the process. Is that possible ?
> Here is the trigger. I want to insert the Network Address value into the
> Lastuser coulumn in the Contact1del table
> CREATE TRIGGER C1Delete ON Contact1
> FOR DELETE
> AS
>
> insert into Contact1DEL (ACCOUNTNO, COMPANY, CONTACT, LASTNAME,
> DEPARTMENT,
> TITLE, SECR, PHONE1, PHONE2, PHONE3, FAX, EXT1, EXT2, EXT3, EXT4,
> ADDRESS1,
> ADDRESS2, ADDRESS3, CITY, STATE, ZIP, COUNTRY, DEAR, SOURCE, KEY1, KEY2,
> KEY3, KEY4, KEY5, STATUS, MERGECODES, CREATEBY,
> CREATEON, CREATEAT, OWNER, LASTUSER, LASTDATE, LASTTIME, RECID)
> select ACCOUNTNO, COMPANY, CONTACT, LASTNAME, DEPARTMENT, TITLE, SECR,
> PHONE1, PHONE2, PHONE3, FAX, EXT1, EXT2, EXT3, EXT4, ADDRESS1,
> ADDRESS2, ADDRESS3, CITY, STATE, ZIP, COUNTRY, DEAR, SOURCE, KEY1, KEY2,
> KEY3, KEY4, KEY5, STATUS, MERGECODES, CREATEBY,
> CREATEON, CREATEAT, OWNER, LASTUSER, GETDATE(), LASTTIME, RECID
> from deleted
> Thanks
>|||Awesome. Thanks everyone. I modified the trigger as follows. Anyone see a
problem with that ? Thanks again!!
insert into Contact1DEL (ACCOUNTNO, COMPANY, CONTACT, LASTNAME, DEPARTMENT,
TITLE, SECR, PHONE1, PHONE2, PHONE3, FAX, EXT1, EXT2, EXT3, EXT4, ADDRESS1,
ADDRESS2, ADDRESS3, CITY, STATE, ZIP, COUNTRY, DEAR, SOURCE, KEY1, KEY2,
KEY3, KEY4, KEY5, STATUS, MERGECODES, CREATEBY,
CREATEON, CREATEAT, OWNER, LASTUSER, LASTDATE, LASTTIME, RECID)
select ACCOUNTNO, COMPANY, CONTACT, LASTNAME, DEPARTMENT, TITLE, SECR,
PHONE1, PHONE2, PHONE3, FAX, EXT1, EXT2, EXT3, EXT4, ADDRESS1,
ADDRESS2, ADDRESS3, CITY, STATE, ZIP, COUNTRY, DEAR, SOURCE, KEY1, KEY2,
KEY3, KEY4, KEY5, STATUS, MERGECODES, CREATEBY,
CREATEON, CREATEAT, OWNER, (select net_address from master..sysprocesses
where spid=@.@.spid), GETDATE(), LASTTIME, RECID
from deleted|||I ordinarily advise against using WITH(NOLOCK), because it can cause queries
to return incorrect answers, but this is a special case in which the row in
sysprocesses tied to @.@.SPID cannot be changed by activity on any another
spid, so it makes better sense to eliminate the extra shared lock.
I would save off net_address and GETDATE() in local variables before
executing the INSERT. That minimizes the number of database accesses and
could make it possible to identify what was changed by the statement that
caused the trigger to fire. Of course, that would work only if you stored
@.@.SPID, too.
DECLARE @.net_address nchar(12), @.getdate DATETIME
SELECT @.net_address = net_address, @.getdate = GETDATE() from
master..sysprocesses WITH(NOLOCK) WHERE spid = @.@.SPID
"jenks" <jenks@.discussions.microsoft.com> wrote in message
news:F382F996-E346-4815-ACCF-42AD3845008F@.microsoft.com...
> Awesome. Thanks everyone. I modified the trigger as follows. Anyone see a
> problem with that ? Thanks again!!
> insert into Contact1DEL (ACCOUNTNO, COMPANY, CONTACT, LASTNAME,
> DEPARTMENT,
> TITLE, SECR, PHONE1, PHONE2, PHONE3, FAX, EXT1, EXT2, EXT3, EXT4,
> ADDRESS1,
> ADDRESS2, ADDRESS3, CITY, STATE, ZIP, COUNTRY, DEAR, SOURCE, KEY1, KEY2,
> KEY3, KEY4, KEY5, STATUS, MERGECODES, CREATEBY,
> CREATEON, CREATEAT, OWNER, LASTUSER, LASTDATE, LASTTIME, RECID)
> select ACCOUNTNO, COMPANY, CONTACT, LASTNAME, DEPARTMENT, TITLE, SECR,
> PHONE1, PHONE2, PHONE3, FAX, EXT1, EXT2, EXT3, EXT4, ADDRESS1,
> ADDRESS2, ADDRESS3, CITY, STATE, ZIP, COUNTRY, DEAR, SOURCE, KEY1, KEY2,
> KEY3, KEY4, KEY5, STATUS, MERGECODES, CREATEBY,
> CREATEON, CREATEAT, OWNER, (select net_address from master..sysprocesses
> where spid=@.@.spid), GETDATE(), LASTTIME, RECID
> from deleted
>
>
deletes a record. I am looking for a way to capture the Network Address valu
e
that is associated with the process. Is that possible ?
Here is the trigger. I want to insert the Network Address value into the
Lastuser coulumn in the Contact1del table
CREATE TRIGGER C1Delete ON Contact1
FOR DELETE
AS
insert into Contact1DEL (ACCOUNTNO, COMPANY, CONTACT, LASTNAME, DEPARTMENT,
TITLE, SECR, PHONE1, PHONE2, PHONE3, FAX, EXT1, EXT2, EXT3, EXT4, ADDRESS1,
ADDRESS2, ADDRESS3, CITY, STATE, ZIP, COUNTRY, DEAR, SOURCE, KEY1, KEY2,
KEY3, KEY4, KEY5, STATUS, MERGECODES, CREATEBY,
CREATEON, CREATEAT, OWNER, LASTUSER, LASTDATE, LASTTIME, RECID)
select ACCOUNTNO, COMPANY, CONTACT, LASTNAME, DEPARTMENT, TITLE, SECR,
PHONE1, PHONE2, PHONE3, FAX, EXT1, EXT2, EXT3, EXT4, ADDRESS1,
ADDRESS2, ADDRESS3, CITY, STATE, ZIP, COUNTRY, DEAR, SOURCE, KEY1, KEY2,
KEY3, KEY4, KEY5, STATUS, MERGECODES, CREATEBY,
CREATEON, CREATEAT, OWNER, LASTUSER, GETDATE(), LASTTIME, RECID
from deleted
ThanksHOST_NAME() maybe?|||jenks wrote:
> I have a simple trigger that writes out records to another table when
> a user deletes a record. I am looking for a way to capture the
> Network Address value that is associated with the process. Is that
> possible ?
> Here is the trigger. I want to insert the Network Address value into
> the Lastuser coulumn in the Contact1del table
> CREATE TRIGGER C1Delete ON Contact1
> FOR DELETE
> AS
>
> insert into Contact1DEL (ACCOUNTNO, COMPANY, CONTACT, LASTNAME,
> DEPARTMENT, TITLE, SECR, PHONE1, PHONE2, PHONE3, FAX, EXT1, EXT2,
> EXT3, EXT4, ADDRESS1, ADDRESS2, ADDRESS3, CITY, STATE, ZIP, COUNTRY,
> DEAR, SOURCE, KEY1, KEY2, KEY3, KEY4, KEY5, STATUS, MERGECODES,
> CREATEBY,
> CREATEON, CREATEAT, OWNER, LASTUSER, LASTDATE, LASTTIME, RECID)
> select ACCOUNTNO, COMPANY, CONTACT, LASTNAME, DEPARTMENT, TITLE, SECR,
> PHONE1, PHONE2, PHONE3, FAX, EXT1, EXT2, EXT3, EXT4, ADDRESS1,
> ADDRESS2, ADDRESS3, CITY, STATE, ZIP, COUNTRY, DEAR, SOURCE, KEY1,
> KEY2, KEY3, KEY4, KEY5, STATUS, MERGECODES, CREATEBY,
> CREATEON, CREATEAT, OWNER, LASTUSER, GETDATE(), LASTTIME, RECID
> from deleted
> Thanks
Select
net_address
From
master..sysprocesses
Where
spid = @.@.spid
This will give you the mac address for the current spid. In your case,
you could use an embedded SELECT or grab the mac address before the
insert. For example:
Insert into #ttt (col1, col2)
Select 1, (
Select
net_address
From
master..sysprocesses
Where
spid = @.@.spid )
David Gugick - SQL Server MVP
Quest Software|||this might help:
select net_address from master..sysprocesses where spid=@.@.spid
dean
"jenks" <jenks@.discussions.microsoft.com> wrote in message
news:8B260AE5-DAF4-43CD-8A29-9232573A39EE@.microsoft.com...
>I have a simple trigger that writes out records to another table when a
>user
> deletes a record. I am looking for a way to capture the Network Address
> value
> that is associated with the process. Is that possible ?
> Here is the trigger. I want to insert the Network Address value into the
> Lastuser coulumn in the Contact1del table
> CREATE TRIGGER C1Delete ON Contact1
> FOR DELETE
> AS
>
> insert into Contact1DEL (ACCOUNTNO, COMPANY, CONTACT, LASTNAME,
> DEPARTMENT,
> TITLE, SECR, PHONE1, PHONE2, PHONE3, FAX, EXT1, EXT2, EXT3, EXT4,
> ADDRESS1,
> ADDRESS2, ADDRESS3, CITY, STATE, ZIP, COUNTRY, DEAR, SOURCE, KEY1, KEY2,
> KEY3, KEY4, KEY5, STATUS, MERGECODES, CREATEBY,
> CREATEON, CREATEAT, OWNER, LASTUSER, LASTDATE, LASTTIME, RECID)
> select ACCOUNTNO, COMPANY, CONTACT, LASTNAME, DEPARTMENT, TITLE, SECR,
> PHONE1, PHONE2, PHONE3, FAX, EXT1, EXT2, EXT3, EXT4, ADDRESS1,
> ADDRESS2, ADDRESS3, CITY, STATE, ZIP, COUNTRY, DEAR, SOURCE, KEY1, KEY2,
> KEY3, KEY4, KEY5, STATUS, MERGECODES, CREATEBY,
> CREATEON, CREATEAT, OWNER, LASTUSER, GETDATE(), LASTTIME, RECID
> from deleted
> Thanks
>|||Awesome. Thanks everyone. I modified the trigger as follows. Anyone see a
problem with that ? Thanks again!!
insert into Contact1DEL (ACCOUNTNO, COMPANY, CONTACT, LASTNAME, DEPARTMENT,
TITLE, SECR, PHONE1, PHONE2, PHONE3, FAX, EXT1, EXT2, EXT3, EXT4, ADDRESS1,
ADDRESS2, ADDRESS3, CITY, STATE, ZIP, COUNTRY, DEAR, SOURCE, KEY1, KEY2,
KEY3, KEY4, KEY5, STATUS, MERGECODES, CREATEBY,
CREATEON, CREATEAT, OWNER, LASTUSER, LASTDATE, LASTTIME, RECID)
select ACCOUNTNO, COMPANY, CONTACT, LASTNAME, DEPARTMENT, TITLE, SECR,
PHONE1, PHONE2, PHONE3, FAX, EXT1, EXT2, EXT3, EXT4, ADDRESS1,
ADDRESS2, ADDRESS3, CITY, STATE, ZIP, COUNTRY, DEAR, SOURCE, KEY1, KEY2,
KEY3, KEY4, KEY5, STATUS, MERGECODES, CREATEBY,
CREATEON, CREATEAT, OWNER, (select net_address from master..sysprocesses
where spid=@.@.spid), GETDATE(), LASTTIME, RECID
from deleted|||I ordinarily advise against using WITH(NOLOCK), because it can cause queries
to return incorrect answers, but this is a special case in which the row in
sysprocesses tied to @.@.SPID cannot be changed by activity on any another
spid, so it makes better sense to eliminate the extra shared lock.
I would save off net_address and GETDATE() in local variables before
executing the INSERT. That minimizes the number of database accesses and
could make it possible to identify what was changed by the statement that
caused the trigger to fire. Of course, that would work only if you stored
@.@.SPID, too.
DECLARE @.net_address nchar(12), @.getdate DATETIME
SELECT @.net_address = net_address, @.getdate = GETDATE() from
master..sysprocesses WITH(NOLOCK) WHERE spid = @.@.SPID
"jenks" <jenks@.discussions.microsoft.com> wrote in message
news:F382F996-E346-4815-ACCF-42AD3845008F@.microsoft.com...
> Awesome. Thanks everyone. I modified the trigger as follows. Anyone see a
> problem with that ? Thanks again!!
> insert into Contact1DEL (ACCOUNTNO, COMPANY, CONTACT, LASTNAME,
> DEPARTMENT,
> TITLE, SECR, PHONE1, PHONE2, PHONE3, FAX, EXT1, EXT2, EXT3, EXT4,
> ADDRESS1,
> ADDRESS2, ADDRESS3, CITY, STATE, ZIP, COUNTRY, DEAR, SOURCE, KEY1, KEY2,
> KEY3, KEY4, KEY5, STATUS, MERGECODES, CREATEBY,
> CREATEON, CREATEAT, OWNER, LASTUSER, LASTDATE, LASTTIME, RECID)
> select ACCOUNTNO, COMPANY, CONTACT, LASTNAME, DEPARTMENT, TITLE, SECR,
> PHONE1, PHONE2, PHONE3, FAX, EXT1, EXT2, EXT3, EXT4, ADDRESS1,
> ADDRESS2, ADDRESS3, CITY, STATE, ZIP, COUNTRY, DEAR, SOURCE, KEY1, KEY2,
> KEY3, KEY4, KEY5, STATUS, MERGECODES, CREATEBY,
> CREATEON, CREATEAT, OWNER, (select net_address from master..sysprocesses
> where spid=@.@.spid), GETDATE(), LASTTIME, RECID
> from deleted
>
>
CAPTURE DML FROM TRIGGER
Hi All!
SQL Server 2000
I've situation where I've to capture a DML executing against let say Table1 and later may at the end of the day or week I want to be able to extract all the DML executed against Table1 and execute them against similar tables on different sql server to synchronize the data. I don't want to use profiler as this is quite expensive resource for my problem niether can use any third party tool.
Is it possible to capture sql statement in the trigger?
I hope I made my question clear. Urgent help will be highly appreciated.
--Sohail.
There is no way to obtain this information accurately in SQL Server right now. So you will have to run a Profiler trace on the desired table and filter the statements. We are considering adding support for obtaining the DML statement that caused a trigger to fire in a future release.|||If you're just capturing the DML to replicate it on a different server then do you need the actual DML?
Why not just a trigger to log which rows have been updated then when you run your replication batch you can easily copy the relavent data over (or delete where appropriate)
Subscribe to:
Posts (Atom)