Cascading Updates/Deletes With Triggers
May 19, 2000
Microsoft article Q142480 states "Triggers cannot be used to perform cascading updates and deletes if ForeignKey-to-PrimaryKey relationships have been extablished using SQL Server's DRI."
Does this mean that I cannot declare FK's in my scripts if I want to have triggers in the table? Do I just add a column in my table that will have a foreign key in it, but just not reference it in my script? Can someone clarify for me?
Thanks,
Nathan
View 1 Replies
ADVERTISEMENT
Aug 17, 2005
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.
View 2 Replies
View Related
Dec 21, 1998
I need some suggestions concerning the issue cascading deletes in a self-referencing table, i.e. a table with a foreign key pointing at the primary key in itself. Although SQL Server still does not support cascading deletes the declarative way there are some other ways to handle this situation. One common way is to use trigger coding with the simple structure
CREATE TRIGGER DelCascadeTrig
ON self_ref_tab
FOR DELETE
AS
DECLARE @C_FK xxxxxx
SELECT @C_FK = C_FK FROM DELETED
BEGIN
DELETE self_ref_tab
FROM self_ref_tab, deleted
WHERE self-_ref_tabC_FK = deleted.C_PK
END
where C_PK is the primary key column and C_FK the foreign key column. The problem is that this simple pattern does not work with self-referencing tables, because the removal of dependent rows deeper layer (n-2, n-3 etc. if the originating delete request is level n and the first level of dependant deletes handled by the trigger code is n-1) would require the delete trigger to fire more than once for the same delete operation. As far as I know, in ver. 6.x triggers are executed only once per SQL statement and in this case the n-2, n-3 etc. level rows would have been left as "orphans".
In ver. 7.0 I suppose this should work fine because of the new recursive trigger execution possibility (trigger will fire up to 32 recursive times per SQL statement), but in the meanwhile (i.e. my case util we have upgraded all our servers) the delete logic for a cascading, self-referencing relationship must be handled completely within one execution of the trigger.
My question is now: does anyone know anything about any common algorithm or trigger code example solving this problem.
Thanks in advance
Bjorn Ehnberg
View 1 Replies
View Related
May 24, 2004
When I setup a relationship in Access I can specify that Primary Key deletes cascade down to the Forgien Key. So when I delete an Order Header it cleans up all the items in the Order Details table for me automatically.
Can I get this same functionality in SQL Server 7 without having to write triggers or are triggers the only way?
thanks
dog
View 13 Replies
View Related
Feb 7, 2008
I haven't used cascading deletes in the past but we're starting a new database and it seems like a good way to go to keep data clean. Or at least it did seem like a good way until I ran some tests. I have 3 tables.
People PeopleEmails Email
(pk)peopleid (pk)peopleid (pk)emailid
fname (pk)emailid address
lname emailtype
password
In this structure, the peopleemails table is simply an association table between the email and people table. I have setup up relationships in a diagram so that when a person is deleted, it cascades to peopleemails and removes the entry there. I also had a cascade set up hoping that when an entry was deleted from peopleemails, it would remove it from the email table but this is not happening. The relationship between email and peopleemail is primary key table email.emailid and foreign key table peopleemail.emailid. Is there a way to get this to work to remove the email address if a peopleemail entry is removed? Thanks.
View 5 Replies
View Related
Aug 31, 2006
I have the following tableCREATE TABLE [tbl_Items]([item_id] int IDENTITY(1,1) CONSTRAINT PK_tbl_Items__item_idPRIMARY KEY,[parent_id] int DEFAULT(NULL) CONSTRAINTFK_tbl_Items__item_id__parent_id REFERENCES [tbl_Items]( [item_id] ) ONDELETE NO ACTION ON UPDATE NO ACTION)My Intention was to create a table that when I delete a record, allrecords that have on the [parent_id] field the deleted record[item_id].I am trying to avoid having to use triggers or create a storedprocedure that firsts delete the children (recursively) and thendeletes the parent.Is there any way to do this by changing my table definition here?
View 1 Replies
View Related
Jul 20, 2005
Hi, I'm using SQL server 2000, and I have set up two tables, table Aand table B. Table A and B have a foreign key constraint such that ifan entry is deleted in table A, then all the entries in table Bassociated with that entry are deleted as well. I imagine that I canfind out the number of records that are deleted in table A when Iexecute the SQL, but is there an easy way of determining the number ofrecords that will be deleted in table B?Thanks,Dan
View 1 Replies
View Related
Feb 27, 2001
It appears that SQL7 does not support options to set cascade updates and deletes in the Relations tab of the property sheet, but it is included in
View 4 Replies
View Related
Mar 1, 2006
I have transactional replication set up between two SQL Server 2000 databases. In some cases when I perform an UPDATE on a published table on the the publisher, SQL Server attempts to perform a DELETE followed by an INSERT on the subscriber using the stored procedures created during the initial snapshot.
Why does it do this?
How can I stop it doing this and force an UPDATE on the publisher to call the UPDATE procedure on the subscriber?
Thanks
View 3 Replies
View Related
Jul 15, 1999
Is it possible to perform a cascading delete and update using TRIGGERS on a table referenced by a foreign key constraint.?To be more specific.. if the primary key is deleted does the delete trigger on the primary table deletes the record in the foreign key table or does it return an error??
if possible please send us the T SQL Statements .
Thanks in Advance
Geenu
Ajaz Dawre
View 2 Replies
View Related
Jun 8, 2007
Ok, I think this may have a simple answer. Basically I have no problems in setting up QueryString/Control/etc parameters when I use SELECT in the Configure Data Source Wizard as it prompts me for the necessary parameters. But when I try to use the Configure Data Source Wizard with an UPDATE, INSERT or DELETE it does NOT prompt me for the required parameters.Is this a bug or am I just missing something? Do I have to put them in manually or something?Thanks!
View 5 Replies
View Related
Jan 3, 2008
I have a 14GB database whose data content is legacy and is described as static. The log file is significantly large and continues to change size mostly increasing by 2-5GB a day (~60GB now) I have observed over the past two days; it shrank once unexpectly by a few GB. The instance is hosting other databases such as: EnterpriseVaultDirectory, EnterpriseVaultMonitoring, EnterpriseVaultStore, and NetPerfMon - might these seemingly unrelated data sources be involved?
I am trying to a trace to find traffic against the tables, no such luck.
Web applications are playing against it for queries but there should be no UPDATEs beign applied. I can only suspect that other unknown applications are performing operations but have yet to find unexplained connections.
Are there any other reasons why this type of log file activity would happen merely due to queries or stored procedure calls?
Lets also state, "mirroring, indexing, replication" are not at play. I know logging "Full" is not necessary as "Simple" should suffice but I am still hunting down why UPDATEs might be getting through. I realize I might adjust the migrated SQL 2000 security model to deny updates to find what breaks but would rather not take that iniative yet.
The installation is a fresh SQL 2005 Standard setup with SP2 applied; the databases were upgraded.
View 6 Replies
View Related
Aug 5, 2004
Here's my table setup:
tblSteps:
StepID int IDENTITY
ParentStepID int ALLOWSNULLS
OtherID int
Amount money
tblOther:
OtherID int IDENTITY
Amount money
Now, I have a trigger defined on tblSteps:
Code:
CREATE TRIGGER tgrUpdateAmount
ON dbo.tblSteps
AFTER INSERT, UPDATE, DELETE
AS
BEGIN
DECLARE @Amount money
SET @Amount = ISNULL((SELECT SUM([Amount]) FROM inserted), 0) - ISNULL((SELECT SUM([Amount]) FROM deleted), 0)
IF (SELECT [ParentStepID] FROM inserted) IS NULL
BEGIN
UPDATE tblOther SET [Amount] = [Amount] + @Amount WHERE [OtherID] = (SELECT [OtherID] FROM inserted)
END
ELSE
BEGIN
UPDATE tblSteps SET [Amount] = [Amount] + @Amount WHERE [StepID] = (SELECT [ParentStepID] FROM inserted)
END
END
What this code basically does is this: if you update the amount of a Step that has a ParentStepID, it will take what was addded (or deleted etc...) and update it's parent with the added amount. If the Step does not have a ParentStepID, it will take the amount add add it to the Other row it corresponds to.
Here's an example of some test data:
tblOther:
OtherID: 1
Amount: 0
tblSteps:
StepID: 1
ParentStepID: NULL
Amount: 0
------
StepID: 2
ParentSTepID: 1
Amount: 0
If I update the Amount to 100 for StepID=2, it also updates the amount for StepID=1. If I update the amount for StepID=1, it also updates the amount for OtherID=1. However, when I update the amount for StepID=2, it does not cascade up to the tblOther level (e.g. Updating StepID=2 should update StepID=1 which should update OtherID=1, right?)
Basically, the trigger isn't cascading. Can anyone point out what I need to do?
Thanks
View 1 Replies
View Related
Sep 14, 2007
I was hoping someone could explain how cascading deletes work in Peer to Peer replication on SQL 2005 SP2, the child tables are also being replicated.
View 1 Replies
View Related
Nov 14, 2007
Hello All
I am running a statment that updates 248 records in a table. That table has a trigger on update.
The First thing that the trigger does is to declare a number of variables and then set them using the values from the inserted and deleted temp tables.
This works fine when you are updating one row at a time but when you run a batch update it errors.
I believe this is because i am assuming that in a batch update a single row will be updated and then the trigger fired, then the next row and so on.
I now know that this is not the case. Is there a way of linking the 3 variables (see Below) so they all return information form a single row.
Declare@ApplicantId Int,
@NewStatus Int,
@OldStatus Int
Set @ApplicantId = (Select ApplicantID from Inserted)
Set @NewStatus = (Select StatusID from Inserted)
Set @OldStatus = (Select StatusID from Deleted)
Hope that makes sense
Thanks for any help.
Steve
View 2 Replies
View Related
Mar 13, 2002
I have triggers in place on a table that do various checks on data input. It is clear that because of these triggers I cannot do updates on multiple records in this table. When I do, I receive an error that "subquery returned more than one value." Is there anyway to work around this by temporarily turning off triggers or something else?
Many thanks for advice.
View 2 Replies
View Related
Dec 19, 2007
I have a project that consists of a SQL db with an Access front end as the user interface. Here is the structure of the table on which this question is based:
Code Block
create table #IncomeAndExpenseData (
recordID nvarchar(5)NOT NULL,
itemID int NOT NULL,
itemvalue decimal(18, 2) NULL,
monthitemvalue decimal(18, 2) NULL
)
The itemvalue field is where the user enters his/her numbers via Access. There is an IncomeAndExpenseCodes table as well which holds item information, including the itemID and entry unit of measure. Some itemIDs have an entry unit of measure of $/mo, while others are entered in terms of $/yr, others in %/yr.
For itemvalues of itemIDs with entry units of measure that are not $/mo a stored procedure performs calculations which converts them into numbers that has a unit of measure of $/mo and updates IncomeAndExpenseData putting these numbers in the monthitemvalue field. This stored procedure is written to only calculate values for monthitemvalue fields which are null in order to avoid recalculating every single row in the table.
If the user edits the itemvalue field there is a trigger on IncomeAndExpenseData which sets the monthitemvalue to null so the stored procedure recalculates the monthitemvalue for the changed rows. However, it appears this trigger is also setting monthitemvalue to null after the stored procedure updates the IncomeAndExpenseData table with the recalculated monthitemvalues, thus wiping out the answers.
How do I write a trigger that sets the monthitemvalue to null only when the user edits the itemvalue field, not when the stored procedure puts the recalculated monthitemvalue into the IncomeAndExpenseData table?
View 4 Replies
View Related
Oct 30, 2007
Hi...
I have data that i am getting through a dbf file. and i am dumping that data to a sql server... and then taking the data from the sql server after scrubing it i put it into the production database.. right my stored procedure handles a single plan only... but now there may be two or more plans together in the same sql server database which i need to scrub and then update that particular plan already exists or inserts if they dont...
this is my sproc...
ALTER PROCEDURE [dbo].[usp_Import_Plan]
@ClientId int,
@UserId int = NULL,
@HistoryId int,
@ShowStatus bit = 0-- Indicates whether status messages should be returned during the import.
AS
SET NOCOUNT ON
DECLARE
@Count int,
@Sproc varchar(50),
@Status varchar(200),
@TotalCount int
SET @Sproc = OBJECT_NAME(@@ProcId)
SET @Status = 'Updating plan information in Plan table.'
UPDATE
Statements..Plan
SET
PlanName = PlanName1,
Description = PlanName2
FROM
Statements..Plan cp
JOIN (
SELECT DISTINCT
PlanId,
PlanName1,
PlanName2
FROM
Census
) c
ON cp.CPlanId = c.PlanId
WHERE
cp.ClientId = @ClientId
AND
(
IsNull(cp.PlanName,'') <> IsNull(c.PlanName1,'')
OR
IsNull(cp.Description,'') <> IsNull(c.PlanName2,'')
)
SET @Count = @@ROWCOUNT
IF @Count > 0
BEGIN
SET @Status = 'Updated ' + Cast(@Count AS varchar(10)) + ' record(s) in ClientPlan.'
END
ELSE
BEGIN
SET @Status = 'No records were updated in Plan.'
END
SET @Status = 'Adding plan information to Plan table.'
INSERT INTO Statements..Plan (
ClientId,
ClientPlanId,
UserId,
PlanName,
Description
)
SELECT DISTINCT
@ClientId,
CPlanId,
@UserId,
PlanName1,
PlanName2
FROM
Census
WHERE
PlanId NOT IN (
SELECT DISTINCT
CPlanId
FROM
Statements..Plan
WHERE
ClientId = @ClientId
AND
ClientPlanId IS NOT NULL
)
SET @Count = @@ROWCOUNT
IF @Count > 0
BEGIN
SET @Status = 'Added ' + Cast(@Count AS varchar(10)) + ' record(s) to Plan.'
END
ELSE
BEGIN
SET @Status = 'No information was added Plan.'
END
SET NOCOUNT OFF
So how do i do multiple inserts and updates using this stored procedure...
Regards
Karen
View 5 Replies
View Related
May 12, 2008
This isn€™t an problem as such, it€™s more of a debate.
If a table needs a number of update triggers which do differing tasks, should these triggers be separated out or encapsulated into one all encompassing trigger. Speaking in terms of performance, it doesn€™t make much of an improvement doing either depending upon the tasks performed. I was wondering in terms of maintenance and best practice etc. My view is that if the triggers do totally differing tasks they should be a trigger each on their own.
www.handleysonline.com
View 12 Replies
View Related
Jun 11, 2002
Hi,
We are developing a new oltp application using SQL Server 2000 and are debating whether to use "cascade delets" or not. In earlier apps, we had issues using cascade deletes like someone deleted parent by mistake and all child records got deleted OR SQL Server crashed in middle of cascade delete and records were lost or performance became an issue when there were huge # of child records to be deleted,etc.
Are there any recommendations for/against using Cascade deletes and when to use and when NOT to use cascade deletes ?
Thanks
Satish
View 2 Replies
View Related
Mar 22, 2007
I'm wondering if anyone has compared the speed of different kinds of deletes.
Assuming super large tables, I wonder, what is the speed ordering for the following operations:
* Remove a column
* Remove a row
* Remove a table
I'll experiment to see if I can get numbers comparing the speeds, but, if someone's already done the work, I'd love to know
thanks
View 3 Replies
View Related
Mar 23, 2007
Hello,
I have three tables:
CREATE TABLE TASKS
(
SEQNO INTEGER NOT NULL IDENTITY (10000,1),
NAME VARCHAR(20) DEFAULT '' NOT NULL UNIQUE,
PRIMARY KEY (SEQNO)
);
CREATE TABLE JOBS
(
SEQNO INTEGER NOT NULL IDENTITY (10000,1),
NAME VARCHAR(20) DEFAULT '' NOT NULL UNIQUE,
PRIMARY KEY (SEQNO)
);
CREATE TABLE JOBTASKS
(
SEQNO INTEGER NOT NULL IDENTITY (10000,1),
JOBID INTEGER DEFAULT 0 NOT NULL REFERENCES JOBS(SEQNO),
TASKID INTEGER DEFAULT 0 NOT NULL REFERENCES TASKS(SEQNO),
PRIMARY KEY (SEQNO)
);
When I delete a record in JOBS I would like to create a trigger that would delete the related records in JOBTASKS.
This is my attempt; but it does not work.
CREATE TRIGGER JOBS_BEFORE_DELETE
ON JOBS
FOR DELETE
AS BEGIN
DELETE FROM JOBTASKS
WHERE JOBID = (SELECT SEQNO FROM DELETED);
END;
Thanks,
Steve
View 3 Replies
View Related
May 23, 2007
I want to use one stored procedure to delete rows in three different tables - how do I write this for Sql Server 2000 please?
PROCEDURE dbo.DeleteSubmission @C_ID intASDELETE *FROM tblCompaniesWHERE C_ID = @C_ID
DELETE *FROM tblStoresWHERE CS_ID = @C_ID
DELETE *FROM tblDistributorsWHERE CD_ID = @C_ID
RETURN
View 4 Replies
View Related
Oct 22, 2007
I'm hoping someone has seen this before because I have no idea what could be causing it.
I have an SQL 2005 database with multiple tables and several triggers on the various tables all set to run after insert and update.
My program inserts a record into the "items" via a SP that returns the index of the newly added row. The program then inserts a row into another table that is related to items. When the row is inserted into the second table it gets an error that it cannot insert the record because of a foreign key restraint. Checking the items table shows the record that was just inserted in there is now deleted.
The items record is only deleted when I have my trigger on that table enabled. Here is the text of the trigger:
GOSET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOALTER TRIGGER [dbo].[TestTrigger]ON [dbo].[items]AFTER INSERT
AS BEGIN
SET NOCOUNT ON;
INSERT INTO tblHistory(table_name, record_id, is_insert) VALUES ('items', 123, 1)
END
tblHistory's field types are (varchar(50), BigInt, bit).
As you can see there is nothing in the trigger to cause the items record to be deleted, so I have no idea what it could be? Anyone ever see this before?
Thanks in advance!
View 7 Replies
View Related
Jun 14, 2006
I have a logins table, a loginroles table (intermediate), and a roles table
When I delete a login from logins I need to delete the roles for that login from loginroles. I know I have to use cascade deletes, but I cannot find the option in sql 2005.
Any help would be appreciated.
Thank You,Jason
View 3 Replies
View Related
Jun 21, 2001
hello,
Can you give me an example of suppression under DOS, of the files with under-directories by exec cmdshell
thank you in advance
Pascal
View 1 Replies
View Related
Jan 10, 2005
I have new client we changed the file retention period on for log ship on SQL 2000. Now the files are not being deleted. I looked at all the sp's but could not finde the one that actually deletes the log file that was copied over. I could just rebuild log ship but this is on a prod box, was trying to figure out what and when the files get deleted, and by what sp
View 2 Replies
View Related
Oct 19, 2004
Hi
I keep data in four seperate locations, each with different data retention.
the problem is, i keep the publisher "thin" - only 24 hrs of data) the data in the relevant tables is about 250000 a day) and these deletes are blocked at the subscribers by commenting out the body of the repl proc.
this does however cause a burst in bandwith & locks the subscribers, even though no actual delete takes place.
does anyone know if you can actuaklly NOT REPLICATE delete commands for specified tables at all - so that the message of the delete wont even be SENT to the subscriber?
Des
View 6 Replies
View Related
Nov 15, 2006
Hello everybody,
We have a Master table in the Db who's PK is referenced in at least 60 tables as FK.
We want to delete all the dependent records in 1 go without using multiple delete statements or any cursor or loop.
Is it possible? Please advice.
Thanks in advance.
Regards,
Ashish
View 14 Replies
View Related
May 12, 2004
Hello all.
Please help me shed light on the following problem:
I am deleting from 2 tables in a transaction. I experience a deadlock occasionally when deleting.
Here are the tables:
create table TABLE_1 (
ID integer not null,
TYPE_CD varchar(50) not null,
STATUS_ID integer not null,
NAME varchar(500) not null,
COMMENT varchar(500) null,
CREATED_DATE datetime not null,
CREATED_BY varchar(15) not null,
LASTMOD_DATE datetime null,
LASTMOD_BY varchar(15) null,
constraint PK_TABLE_1 primary key nonclustered (ID)
)
create table TABLE_2 (
TASK_ID integer not null,
TABLE_1_ID integer not null,
COMMENT varchar(500) null,
constraint PK_TABLE_2 primary key nonclustered (TASK_ID, TABLE_1_ID)
)
Table table_2 is an intersection table between table_1 and another lookup table (called task).
Whenever I delete from table_1 I need to delete from table_2 to maintain integrity.
The reason I do not have clustered indexes on the tables is b/c they start out empty (every day a housekeeping task deletes from them. Unfortunately I cannot change this...).
In my (web/jsp) application before I delete, I select from table_1 to get data I need to remember after the delete.
In order to avoid the "select for update problem", I use the "UPDLOCK" hint in the select like this:
select *
from table_1 WITH (UPDLOCK),
table_2 WITH (UPDLOCK)
where table_1.id = table_2.table_1_id "
and table_1.id = ?
and table_2.task_id = ?
I then issue my deletes in the following order:
delete from table_1
where task_id = ?
and table_1_id = ?
and then,
delete from table_1 where id = ?
The application sets the isolation level to READ COMMITTED.
The above 3 sql statements are executed as part of a transaction. Occasionally, when I drive the application, I get a deadlock. This happens intermittently. I have been able to reproduce the problem by attempting to delete records really fast, one after the other from the (web) front end.
When the deadlock occurs these are the locks that are held by the running processes:
Blocked SPID (10):
============
Object Lock Type Mode Status Owner Index Resource
app DB S GRANT Sess TABLE_2
app.dbo.TABLE_1 TAB IX GRANT Xact TABLE_2
app.dbo.TABLE_1 KEY U GRANT Xact PK_TABLE_2 (a400f609034c)
app.dbo.TABLE_1 RID U GRANT Xact TABLE_2 1:157:3
app.dbo.TABLE_1 PAG IU GRANT Xact PK_TABLE_2 1:443
app.dbo.TABLE_1 PAG IU GRANT Xact TABLE_2 1:157
app.dbo.TABLE_2 KEY S WAIT Xact PK_TABLE_2 (6501eda29ac1)
app.dbo.TABLE_2 KEY S GRANT Xact PK_TABLE_2 (ef007b1066ea)
app.dbo.TABLE_2 TAB IS GRANT Xact TABLE_2
app.dbo.TABLE_2 PAG IS GRANT Xact PK_TABLE_2 1:252
Blocking SPID (12):
=============
Object Lock Type Mode Status Owner Index Resource
app DB S GRANT Sess TABLE_2
app.dbo.TABLE_2 KEY X GRANT Xact PK_TABLE_2 (6501eda29ac1)
app.dbo.TABLE_2 RID X GRANT Xact TABLE_2 1:176:3
app.dbo.TABLE_2 TAB IX GRANT Xact TABLE_2
app.dbo.TABLE_2 PAG IX GRANT Xact PK_TABLE_2 1:252
app.dbo.TABLE_2 PAG IX GRANT Xact TABLE_2 1:176
I could not figure out what the problem is. Please help me with this.
Any help will be much appreciated.
Thanks in advance
View 2 Replies
View Related
Jul 23, 2005
Hello all. We are just getting started with replication and I'mwondering if there is a way to not have deletes replicated. I know Icould manually remove the delete trigger from every table but I'mintrested in have this a bit more automatic (like when its creating thesubscription).ThanksStephen
View 1 Replies
View Related
Apr 28, 2008
Hi guys,
A simple question of good practices: is it better to have a cascade update/delete on a table? Or is it better to do it "by hand" in a SP?
Thanks a lot.
View 1 Replies
View Related
