I am having great difficulty with cascading deletes, delete triggers and referential integrity.
The database is in First Normal Form.
I have some tables that are child tables with two foreign keyes to two different parent tables, for example:
Table A / Table B Table C / Table D
So if I try to turn on cascading deletes for A/B, A/C, B/D and C/D relationships, I get an error that I cannot have cascading delete because it would create multiple cascade paths. I do understand why this is happening. If I delete a row in Table A, I want it to delete child rows in Table B and table C, and then child rows in table D as well. But if I delete a row in Table C, I want it to delete child rows in Table D, and if I delete a row in Table B, I want it to also delete child rows in Table D.
SQL sees this as cyclical, because if I delete a row in table A, both table B and table C would try to delete their child rows in table D.
Ok, so I thought, no biggie, I'll just use delete triggers. So I created delete triggers that will delete child rows in table B and table C when deleting a row in table A. Then I created triggers in both Table B and Table C that would delete child rows in Table D.
When I try to delete a row in table A, B or C, I get the error "Delete Statement Conflicted with COLUMN REFERENCE". This does not make sense to me, can anyone explain? I have a trigger in place that should be deleting the child rows before it attempts to delete the parent row...isn't that the whole point of delete triggers?????
This is an example of my delete trigger:
CREATE TRIGGER [DeleteA] ON A FOR DELETE AS Delete from B where MeetingID = ID; Delete from C where MeetingID = ID;
And then Table B and C both have delete triggers to delete child rows in table D. But it never gets to that point, none of the triggers execute because the above error happens first.
So if I then go into the relationships, and deselect the option for "Enforce relationship for INSERTs and UPDATEs" these triggers all work just fine. Only problem is that now I have no referential integrity and I can simply create unrestrained child rows that do not reference actual foreign keys in the parent table.
So the question is, how do I maintain referential integrity and also have the database delete child rows, keeping in mind that the cascading deletes will not work because of the multiple cascade paths (which are certainly required).
Hi all.I have used a SqlDataSource in my page with this delete command:DELETE FROM tblPersonnel WHERE (ID = @original_ID)and the "OldValueParameterFormatSring" property of the datasource is "original_{0}".and i also have a GridView and a button for delete in rows.(it's CommandName is "Delete"). But when i try to delete a record, the record does not get deleted and this button only makes a PostBack on the page! Why doesn't it work? Thanks in advance.
i am having problem running a simple delete statement against a table. it just hangs is there anything i should look at? the table has 4 primary keys and the index makes up of the 4 keys and ideas?
i viewed the delete statement with the execusion plan and this is what i saw.
delete -> index delete/delete -> sorting the input -> table delete/delete -> Top -> Index scan.
Hi all, I am writing a test result database in SQL 2K5 and one of the features I want to implement is a stored procedure that deletes the oldrecords while preserving a set number of records, the following is what my SP looks like
Procedure [dbo].[CleanResults]
@RecsToKeep bigInt,
@Output Int output
as
Declare @Date Datetime
Declare @Count bigint
set @Date = getdate()
Select @Count = Count(UniqueID) from [Main]
Print 'THE COUNT IS'
print @Count
if ( @Count > @RecsToKeep) begin
set @Count= @Count - @RecsToKeep
Print 'THE Number to delete is'
print @Count
select TOP(@Count) UniqueID from Main order By [Main].[TestDateTime] desc
Delete from [Main] where exists (select TOP(@Count) * from Main order By [Main].[TestDateTime] desc);
set @Output = @Count end
else begin
set @Output = -1
end
whats odd is that the select staement will evaluate correctly and return the oldest record @Count record, however the delete stament removes all the records. An advice would be appriciated.
Thanks Christopher
PS Any advice for using TOP with variables in MSDE 2k (as opposed to 2k5) would be appreciated
why does this trigger not work? i'm winging this and hoping for some input. nobody's responded to my earlier posts. I get no errors, but no update either. CREATE TRIGGER UpdateLineTotalON CartItemsFOR UPDATEASDECLARE @lineTotal Numeric(9)DECLARE @itemSell Numeric(9)DECLARE @pounds Numeric(9)SELECT * FROM CartItemsSET @lineTotal = @pounds * @itemSell
I'm using SQL Server 2005 (product version = 9.00.1406.00, product level = RTM, and edition = Developer Edition). I have a db with a number of tables; I created a Foreign Key in one table and added a Foreign Key w/ ON DELETE CASCADE to it -- all using Microsoft SQL Server Management Studio. When I delete the record in the table with the foreign key, the record in the other table does not get deleted. I tried doing this with a simple SQL script in Microsoft SQL Server Management Studio and in a simple .Net / ADO (C#-based) program. Only the record in the table that I'm specifically deleting is deleted.
Here's the table that is referenced by the foreign key (I told Server Management Studio to write out the script):
USE [CHNOPSDb] GO /****** Object: Table [dbo].[tblDeviceContainer] Script Date: 08/13/2007 16:47:03 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[tblDeviceContainer]( [ID] [int] IDENTITY(1,1) NOT NULL, [DeviceContainerTypeID] [int] NOT NULL, CONSTRAINT [PK_tblDeviceContainer] PRIMARY KEY CLUSTERED ( [ID] ASC )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY] ) ON [PRIMARY]
Here's the table that has the foreign key to the above table (again, I told Management Studio to write out the script):
USE [CHNOPSDb] GO /****** Object: Table [dbo].[tblNode] Script Date: 08/13/2007 16:46:40 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO CREATE TABLE [dbo].[tblNode]( [ID] [int] IDENTITY(1,1) NOT NULL, [NodeName] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [NodeTypeID] [int] NOT NULL, [UnitID] [int] NOT NULL, [pDeviceContainerID] [int] NOT NULL, [NodeIndex] [int] NULL, CONSTRAINT [PK_Node] PRIMARY KEY CLUSTERED ( [ID] ASC )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY] ) ON [PRIMARY] GO SET ANSI_PADDING OFF GO USE [CHNOPSDb] GO ALTER TABLE [dbo].[tblNode] WITH CHECK ADD CONSTRAINT [FK_tblNode_tblDeviceContainer] FOREIGN KEY([pDeviceContainerID]) REFERENCES [dbo].[tblDeviceContainer] ([ID]) ON DELETE CASCADE
I then perform a delete using the following:
Use CHNOPSDb;
delete from tblNode where ID = 1;
It deletes the tblNode record but doesn't delete the tblDeviceContainer record that is referenced by tblNode.
I wanted to create a new trigger, but Enterprise Manager tells me about an "Incorrect syntax near @UpdatedByID, line 28". I double-checked everything, but it still does not work :mad: .
Any hints :confused: ?
TIA,
-Gernot
Here is the statement (line 28 is marked with ***):
CREATE TRIGGER TransferToABII ON [dbo].[CALGeneral] FOR INSERT AS BEGIN TRANSACTION BEGIN DECLARE @Event varchar(255), @BBaseUID int, @StartDate smalldatetime, @EndDate smalldatetime, @Details varchar(255), @AddressID int, @ProjectID int, @UpdatedByID int, @ActID int, @EventID int
BEGIN EXEC BrainBase.dbo.BB_NEW_CREATE_NoteTask_Ret *** (@UpdatedByID, @AddressID, @ProjectID, @BBaseUID, @StartDate, GetDate(), @Event, NULL, NULL, NULL, NULL, @Details text, @ActID = @ActID OUTPUT, @EventID = @EventID OUTPUT) END BEGIN UPDATE CALGeneral SET ActID = @ActID WHERE ID = INSERTED.ID END END
IF @@ERROR <> 0 BEGIN RAISERROR('Error occured',16,1) ROLLBACK TRANSACTION END COMMIT TRANSACTION
Hi, i defined a sqldatasource in VWD manually (option specify sql statement) because several tables are involved. I also need a Delete statement, so i defined it also manually. The select and delete statement are : <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:test %>" SelectCommand="SELECT aspnet_Users.UserName as lid, aspnet_Roles.RoleName as categorie, aspnet_Users.beheerder as beheerder, aspnet_Membership.Email as emailadres FROM aspnet_Users INNER JOIN aspnet_Membership ON aspnet_Users.UserId = aspnet_Membership.UserId INNER JOIN aspnet_UsersInRoles ON aspnet_Users.UserId = aspnet_UsersInRoles.UserId inner JOIN aspnet_Roles ON aspnet_UsersInRoles.RoleId = aspnet_Roles.RoleId order by username" DeleteCommand="delete from aspnet_users where userid=@userid"> <DeleteParameters> <asp:Parameter Name="userid" /> </DeleteParameters> </asp:SqlDataSource> My problem is that the Select works, but not the Delete. Any idea why? Thanks tartuffe
Hi all, I have a problem with this trigger. It seams to be very simple, but it doesn't work...
I created a trigger on a table and I would want that this one updates a field of a table on a diffrent DB (Intranet). When I test it normally (a real situation), it doesn't work, but when I do an explicit update ("UPDATE AccesCard SET LastMove = getDate();" by example) it works.
If anyone could help me, I would appreciate.
NB: Is there a special way, in a trigger, to update a table when the table to update is on another BD ?
Francois
This is the trigger: ------------------------------------------------------------
ALTER TRIGGER UStatus ON AccesCard AFTER UPDATE, INSERT AS
DECLARE @noPerson int
SET NOCOUNT OFF
IF UPDATE(LastMove) BEGIN SELECT @noPerson = Person FROM INSERTED UPDATE Intranet.dbo._Users SET Intranet.dbo._Users.status = 1 WHERE personNo = @noPerson; END
The UpdateTrackingCS program will call a stored procedure to get the inserted data and update other databases. And the reason to put the commit statement in sql2000 is to have sql commit the transaction, so the store procedure in UpdateTrackingCS can query thtat inserted records.
When I use the same code in SQL2005, no matter what I do the UpdateTrackingCS program cannot query the data by the UPSID. It always says record not found.
I have tried to change commit to begin trans ... commit trans. But nothing works. Please help!
The UpdateTrackingCS program will call a stored procedure to get the inserted data and update other databases. And the reason to put the commit statement in sql2000 is to have sql commit the transaction, so the store procedure in UpdateTrackingCS can query thtat inserted records.
When I use the same code in SQL2005, no matter what I do the UpdateTrackingCS program cannot query the data by the UPSID. It always says record not found.
I have tried to change commit to begin trans ... commit trans. But nothing works. Please help!
I used the toolbox to select maintenance cleanup task to create the job to do this. In reading similar notes regarding this problem, some people mentioned that there was a choice to include subfolders. I do not have this choice. When I execute select @@version I get Microsoft SQL Server 2005 - 9.00.3042.00 (X64) Feb 10 2007 00:59:02 Copyright (c) 1988-2005 Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 2) . This is running on a cluster. Any idea what is going on here? Thanks.
I have a trigger that fires on update, populating a varchar field [ExampleDate_Str] with the dd/mm/yy format of the inserted datetime field [ExampleDate]. It works, but I don't want to rewrite it for the thousands of datetime fields in hundreds of tables in my db. So I am looking for a way to do something like this in a trigger -
for each column in triggered tableset @ColName = (the column name)if datatype(@ColName) = datetimeset @ColName + "_Str" = convert(varchar(8),ExampleDate,3)next
Obviously the above doesn't work in SQL or indeed any other language yet invented - is it possible to make it so that it does, and if so, how? The main points are to read the column name into a variable, check the datatype, and modify the column named @ColName + "_Str".
Hello, For SQL Server 6.5. I need help if there is a way to fire a delete trigger when accidentenlty somebody deletes a record from say table AAA then this record should be inserted in another dump table say Table BBBDump with table schema as table AAA plus some other columns like DB_NAME,Time when recorded deleted from table AAA, user ID .
I put a deleted trigger on this table when a record is deleted, I update a balance in another table based on the deleted amount. This works fine when I am deleting one record at a time, but when I delete more than one record, the first value from the first record is captured to update the balance due and the rest of the of the records has not effect. To elaborat more, delete * from table1 where id =1 and amount =200 -- this works fine delete * from table1 where id =1 and amount =100 -- the first 100 only update the balance in another table the second 100 does nothing, any ideas, I appreciate your help.
I have a SQL statement that deletes a lot of records in a table (PACCESOS_DET) and a Trigger that fires for delete on the table. The Trigger works fine when only one record is deleted but no when more than record is deleted; it only works for 1 and there is no error message. For each row deleted I need to update a column in another table (PACCESOS_CAB). This the trigger...
CREATE TRIGGER ActualizaDiasVisita ON dbo.PACCESOS_DET FOR DELETE AS declare @mdias as int declare @mFKFeria as int declare @mtipo as char(1) declare @mfkcontacto as varchar(7)
if exists( select * from PACCESOS_CAB m join deleted i on m.FKFeria= i.FKFeria and m.FKContacto=i.FKContacto and m.Tipo=i.Tipo) begin select @mfkferia=m.fkferia, @mfkcontacto = m.fkcontacto, @mtipo = m.tipo, @mdias = diasvisita from PACCESOS_CAB m join deleted i on m.FKFeria= i.FKFeria and m.FKContacto=i.FKContacto and m.Tipo=i.Tipo update PACCESOS_CAB set diasvisita = @mdias -1 where FKFeria= @mFKFeria and FKContacto=@mFKContacto and Tipo=@mTipo end
i have created a view from two tables in my database (ProjectedOutputs, and Output)
since I am using the view in vb to fill a grid, i am trying to set up a INSTEAD OF DELETE trigger to delete any records in ProjectedOutputs that may be deleted from my grid. Although the records are deleting OK, so are the records in Output, which i dont want. i am not sure how to stop this, or to see if my trigger is actually firing, I would appreiciate any suggestions
CREATE TRIGGER Test ON V_ProjectOutputs INSTEAD OF DELETE AS
DELETE PROJECTEDOUTPUTS FROM PROJECTEDOUTPUTS JOIN deleted ON PROJECTEDOUTPUTS.ProjectedOutputID = deleted.ProjectedOutputID
Hi, I'm building a website that interacts with MS SQLserver(v7) via a Buisiness Rule-layer developed in COM+ with VB(v6 sp5). The website is an add-on on a existing client/server app. Now I have delete triggers on almost every table and they work perfectly in the c/s app and when runned in the query analyzer. But when I try to do the same with COM+ I get the the next error message on line 10: "Cannot use SAVE TRANSACTION within a distributed transaction."
Here follows the code. Hopefully anybody can help me with this problem.
SQL-statement:
Function getDeleteRequestSQL(ByRef strRequestId As String) As String Dim strSQL As String strSQL = "DELETE FROM thmld2 WHERE right(hdmcode,8)='" & strRequestId & "'" getDeleteRequestSQL = strSQL End Function
Trying to create a trigger on a table that will delete any records in it that do not exist in another table.
Inserting into OrderRebateHistory Table. if ordtype, ord_no, and line_seq_no do not exist in oelinhst then delete from OrderRebateHistory or do not insert.
Right now my code will insert the record. Then when another record gets inserted the previous record is deleted.
CREATE TRIGGER [DeletefromOrderRebateHistory] ON [dbo].[OrderRebateHistory] for INSERT AS
DELETE OrderRebateHistory FROM OrderRebateHistory inner join oelinhst_sql on oelinhst_sql.ord_type = OrderRebateHistory.ord_type and oelinhst_sql.ord_no = OrderRebateHistory.ord_no and oelinhst_sql.line_seq_no = OrderRebateHistory.line_seq_no where oelinhst_sql.ord_type <>OrderRebateHistory.ord_type and oelinhst_sql.ord_no <> OrderRebateHistory.ord_no and oelinhst_sql.line_seq_no <> OrderRebateHistory.line_seq_no
hello,I googled around some time but I found no solution for this issue(SS2000).I have a table tblB which has to be filled whenever in another tabletblA records are inserted, updated or deleted.So I created one trigger which works fine for inserts and updates andfills my tblB. tblB is filled with other fields which I get from aview vwC.The issue is about this view. When in tblA a record is deleted, thecorresponding record in vwC does not exist and I can't fill tblB. Itried around with INSTEAD OF -Trigger and got error message becausetblA has RI cascades so this is not possible. A temp table could bethe right way? Can you show me an example?thanks--candide_sh
hello,I googled around some time but I found no solution for this issue(SS2000).I have a table tblB which has to be filled whenever in table tblArecords are inserted, updated or deleted.So I created one trigger which works fine for inserts and updates andfills my tblB. tblB is filled with other fields which I get from aview vwC. This view vwC is based on a key field used in tblA.The issue is about this view. When in tblA a record is deleted, thecorresponding record in vwC does not exist and I can't fill tblB. Itried around with INSTEAD OF -Trigger and got error message becausetblA has RI cascades so this is not possible. A temp table could bethe right way? Can you show me an example?thanks--candide_sh
For example i've a two table like emp and emp_personal and now what i want to do is
i want to delete one row from emp table so how is it possible to delete automatically that emp's details from second table(emp_personal) ?
Ex:
emp emp_personal
emp_id emp_name emp_basic emp_id emp_address
101 Nagu 32,000 101 India
102 Vijay 35,000 102 South Africa
103 Ritesh 30,000 103 U.S
I want to delete employee who hav emp_id of 102 from emp table , so how can i delete automatically that employee details from second table i.e. emp_personal ?
Hi Everyone, I'm having some trouble with the below trigger. When I add a row of data either manually or using an INSERT query, it just doesn't do anything. It doesn't provide any error messages either. This makes me think that it's aborting the operation because rowsAffected are 0 or some other simple error. My row manipulation code could be suspect also. This is my first time writing a trigger or using T-SQL for that matter. What I'm trying to do is to have the trigger add +1 to the Iter field of all rows where BouleID is equal to the BouleID of the row being inserted. So let's say I have the following table:
Now, if I insert a row with BouleID = A01 and Current Location = Polishing, I want the Iter field of all previous rows to iterate by +1 and this new row to have Iter = 0.
I am using SQL Management Studio Express, and SQL Server Express.
Any thoughts on anything wrong with my selection code and iteration code? Could I adapt this to handle more than one row by using a GROUP BY BouleID somehow?
Code Block
SET ANSI_NULLS ON
GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Description: <this trigger will iterate the Iter field by one for each rows that has the BouleID matching the one of -- the row being inserted> -- ============================================= CREATE TRIGGER dbo.trgCG_DispoIterate$InsertTrigger ON dbo.dbCG_Disposition AFTER INSERT AS BEGIN
DECLARE @rowsAffected int, @msg varchar(2000), --for error message @BouleID varchar(6) -- and do I need to enter more than one?
SET @rowsAffected = @@rowcount
IF (@rowsAffected = 0 or @rowsAffected > 1 ) RETURN --don't continue if no rows changed or doing more than one at a time. -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON SET ROWCOUNT 0
BEGIN TRY
--VALIDATION BLOCK Leave this alone for now
SELECT @BouleID = BouleID FROM Inserted --sets @bouleID equal to the BouleID of the row being inserted.
UPDATE dbo.dbCG_Disposition --This block sets the Iter field to it's previous value +1 SET Iter = ( Iter + 1 ) -- WHERE BouleID = @BouleID --
END TRY BEGIN CATCH IF @@TRANCOUNT >0 ROLLBACK TRANSACTION
--or this will get rolled back EXECUTE dbo.ErrorLog$Insert -- this function creates an errorlog table which gets filled up if there is an error in the try block.
DECLARE @ERROR_MESSAGE nvarchar(4000) SET @ERROR_MESSAGE = ERROR_MESSAGE() RAISERROR (@ERROR_MESSAGE, 16, 1) END CATCH
OK I am really going nuts here. I have a trigger that will work if I insert a row using SQL MGMT Studio or VS DB explorer, but it will not insert using the tableadapter.update() command.
What is also strange is that I have to execute the query in the VS DB Explorer or SSMSE to get them to fill in the default values (trigger).
No matter what they don't work in the debug application:
Below is the trigger:
Code Snippet
ALTER TRIGGER [dbo].[weight_train_log_InsertTrigger] ON [dbo].[weight_train_log] AFTER INSERT AS BEGIN SET NOCOUNT ON UPDATE [weight_train_log] SET [CreationDate] = GETDATE() FROM inserted WHERE inserted.[GUID] = [weight_train_log].[GUID] END
Hi! I am deleting some records from the database and at the same time inserting them into a new table. I am doing all this with sql querries. How can that be done with triggers. Basically on the delete, i'd like to insert the affected records. Thanks! James
On the database that I am maintaining we are having some data integrity issues between our Logon table and another sub table that stores the LogonId.
The best solution would be to put in a foreign key, but that is going to require a lot of work and a lot of code changes for the entire system. This is what we plan to do, but this is not a quick fix. We need something that can be implemented quickly.
The easiest and quickest fix is to check this sub table to see if the LogonId is in the sub table and the row is marked as Active or Working. If it is then we will abort the deletion and raise an error. Otherwise the delete should happen normally.
Is aborting the deletion as simple as :
<code> Delete From deleted Where LogonId = @myId </code>
Hi, I have two tables: table A with key name, version table B with key id and foreing key A.name,A.version.
The realtion A to B is 1:n
I use following trigger to keep data integrity (when record in A deleted, all records with corresponding foreing key should be deleted from table B):
CREATE TRIGGER trDelA ON A FOR DELETE AS DECLARE @n int , @ver int SELECT @n = name , @ver = version FROM deleted DELETE B FROM B,A WHERE @n=B.name AND @ver=B.version
This trigger works fine, except the case that it doesn't fire when last record deleted from A. why does it happens? how can I fix it?
