Cascading Update And Delete
Aug 18, 2007
If we want to maintain the data in relationships.
There are two ways to do it.
1. Auto (Like Cascading Update And Delete)
2. Manually (Like In Stored Procedures)
I read an intresting article
http://imar.spaanjaars.com/QuickDocId.aspx?quickdoc=419
In this article Imar has choosen the second way (Manually).
And when I talk to Imar.
He said, "Cascading deletes would have worked equally well in this situation. However, I personally don't like them too much. I am much rather in control, enabling me to delete what I want and when I want it. I could, for example, keep certain data for "time travelling scenarios" (e.g. the state things were in some time ago) or I might want to keep it for other purposes."
Can any one help me to choose the better one.
Waiting for helpful replies.
View 2 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
Nov 13, 2006
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).
Hope this makes sense...
Thanks,
Josh
View 6 Replies
View Related
Nov 8, 2006
I use SQL Server 2005I have tables tblUserData, tblUsersAndGuestbook, tblGuestbooktblUserdata contains:UserCode intUsername nvarchar(50)tblUsersAndGuestbook contains:Usercode int (FK to tblUserData)GBEntryCode inttblGuestbookGBEntryCode int (FK to tblUsersAndGuestbook)GBText textNow...if I delete a user in tblUserData I want to also delete the entries in tblUsersAndGuestbook AND in tblGuestbook.I've heard something about cascading delete, but how can i configure that in my database?Or do I manually need to delete all entries from code?
View 2 Replies
View Related
Jun 4, 2001
Hi,
I read all the existing material in SWYNK but still am not clear on the following question.
What is the best way to perform Cascading actions (Delete & Update) with foreign Key Constraints declared? We are using SQL Server 7.0
thanks
Rozina
View 1 Replies
View Related
Sep 14, 2004
Procedure spDeleteRows
/*
Recursive row delete procedure.
It deletes all rows in the table specified that conform to the criteria selected,
while also deleting any child/grandchild records and so on. This is designed to do the
same sort of thing as Access's cascade delete function. It first reads the sysforeignkeys
table to find any child tables, then deletes the soon-to-be orphan records from them using
recursive calls to this procedure. Once all child records are gone, the rows are deleted
from the selected table. It is designed at this time to be run at the command line. It could
also be used in code, but the printed output will not be available.
*/
(
@cTableName varchar(50), /* name of the table where rows are to be deleted */
@cCriteria nvarchar(1000), /* criteria used to delete the rows required */
@iRowsAffected int OUTPUT /* number of records affected by the delete */
)
As
set nocount on
declare @cTab varchar(255), /* name of the child table */
@cCol varchar(255), /* name of the linking field on the child table */
@cRefTab varchar(255), /* name of the parent table */
@cRefCol varchar(255), /* name of the linking field in the parent table */
@cFKName varchar(255), /* name of the foreign key */
@cSQL nvarchar(1000), /* query string passed to the sp_ExecuteSQL procedure */
@cChildCriteria nvarchar(1000), /* criteria to be used to delete
records from the child table */
@iChildRows int /* number of rows deleted from the child table */
/* declare the cursor containing the foreign key constraint information */
DECLARE cFKey CURSOR LOCAL FOR
SELECT SO1.name AS Tab,
SC1.name AS Col,
SO2.name AS RefTab,
SC2.name AS RefCol,
FO.name AS FKName
FROM dbo.sysforeignkeys FK
INNER JOIN dbo.syscolumns SC1 ON FK.fkeyid = SC1.id
AND FK.fkey = SC1.colid
INNER JOIN dbo.syscolumns SC2 ON FK.rkeyid = SC2.id
AND FK.rkey = SC2.colid
INNER JOIN dbo.sysobjects SO1 ON FK.fkeyid = SO1.id
INNER JOIN dbo.sysobjects SO2 ON FK.rkeyid = SO2.id
INNER JOIN dbo.sysobjects FO ON FK.constid = FO.id
WHERE SO2.Name = @cTableName
OPEN cFKey
FETCH NEXT FROM cFKey INTO @cTab, @cCol, @cRefTab, @cRefCol, @cFKName
WHILE @@FETCH_STATUS = 0
BEGIN
/* build the criteria to delete rows from the child table. As it uses the
criteria passed to this procedure, it gets progressively larger with
recursive calls */
SET @cChildCriteria = @cCol + ' in (SELECT [' + @cRefCol + '] FROM [' +
@cRefTab +'] WHERE ' + @cCriteria + ')'
print 'Deleting records from table ' + @cTab
/* call this procedure to delete the child rows */
EXEC spDeleteRows @cTab, @cChildCriteria, @iChildRows OUTPUT
FETCH NEXT FROM cFKey INTO @cTab, @cCol, @cRefTab, @cRefCol, @cFKName
END
Close cFKey
DeAllocate cFKey
/* finally delete the rows from this table and display the rows affected */
SET @cSQL = 'DELETE FROM [' + @cTableName + '] WHERE ' + @cCriteria
print @cSQL
EXEC sp_ExecuteSQL @cSQL
print 'Deleted ' + CONVERT(varchar, @@ROWCOUNT) + ' records from table ' + @cTableName
--------
The above code is good .. but has limitation...throws an error:
Server: Msg 217, Level 16, State 1, Procedure spDeleteRows, Line 58
Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32).
Can anyone out there suggest a better way of implementing on a database without a limitation of levels.. we are talking about a HUGE DB with lots of table and FK referentials..
Please advice.. or solve the problem..
Thank you
View 2 Replies
View Related
Jul 20, 2005
I use cascading delete on my SQL Server Database. I am experiencing along query time on my highest level delete, 10 minutes. If I deletefrom each table manually and then delete the parent, I will usually bedone in less than a minute. Any suggestions?
View 1 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
Nov 16, 2006
Edit: Sorry This is OSQL.What I use as my query is:"DELETE FROM timerecord WHERE Actual_Time_In LIKE '11.12.2006%'"The row of Actual_Time_In is formatted with Date and time (MM.DD.YYYY HH:MM:SS) sometimes there are ten records and I'd rather not have to remove them from the table one at a time. However, even though I have a record that is '11.12.2006 22:43:00' my delete doesn't work osql states I have 0 rows affected.This is only MSDE so I don't have anyother way to open the table.Sometimes these records have other records that reference them. Is there anyway to do a cascading delete without it getting to complex?Thanks of all your help, I am just a tech support guy beating his head against a wall..
View 2 Replies
View Related
Oct 3, 2005
hello guysi am using a table that its secondary key connected to its primary key...and as sql server 2000 doesnt allow cascade delete fore such,i had to write a trigger myselfso i wrote the following triggerCREAT TRIGGER nameON tableFOR DeleteASBEGINIF @@ROWCOUNT >0Delete from table where table.parentID in (select sortID from deleted);ENDthen i went to the table and i tried to delete...and it gave me an error....that there are records that have there parentID= sortID of the table i am trieng to delete...so i deleted the relationship...and kept the triggerand now ...when i delete one...it deletes one level down....but not more....i mean when i delete sortID=4it deletes all the records that has parentID=4...and NOT more..whereas my aim was to have it recursive not to have records lost in my databasehope i explained good as much as i hope to find an answer soon...a clear one...and thanks in advanced...
View 3 Replies
View Related
Feb 16, 2008
This function will generate all DELETE statements in correct order to perform a CASCADING delete.
For self-joined tables, it will generate the T-SQL code to "unwind" the table, also in correct order!CREATE FUNCTION dbo.fnCascadingDelete
(
@Schema NVARCHAR(128) = NULL,
@Table NVARCHAR(128) = NULL
)
RETURNS@Return TABLE
(
RowID INT PRIMARY KEY CLUSTERED,
IsSelfJoin TINYINT NOT NULL,
HasPk TINYINT NOT NULL,
[SQL] NVARCHAR(4000) NOT NULL
)
AS
BEGIN
DECLARE@Constraints TABLE
(
RowID INT NOT NULL,
Indent SMALLINT NOT NULL,
[Catalog] NVARCHAR(128) NOT NULL,
[Schema] NVARCHAR(128) NOT NULL,
[Table] NVARCHAR(128) NOT NULL,
[Column] NVARCHAR(128),
pkCatalog NVARCHAR(128),
pkSchema NVARCHAR(128),
pkTable NVARCHAR(128),
pkColumn NVARCHAR(128),
pkType NVARCHAR(128),
pkSize INT,
IsSelfJoin TINYINT NOT NULL,
HasPk TINYINT NOT NULL
)
INSERT@Constraints
(
RowID,
Indent,
[Catalog],
[Schema],
[Table],
[Column],
pkCatalog,
pkSchema,
pkTable,
pkColumn,
pkType,
pkSize,
IsSelfJoin,
HasPk
)
SELECTRowID,
Indent,
[Catalog],
[Schema],
[Table],
[Column],
pkCatalog,
pkSchema,
pkTable,
pkColumn,
pkType,
pkSize,
SelfJoin,
CASE
WHEN [Column] IS NULL THEN 0
ELSE 1
END
FROMdbo.fnTableTree(@Schema, @Table)
IF @@ROWCOUNT = 0
RETURN
DECLARE@SQL TABLE
(
ID INT IDENTITY(1, 1),
RowID INT PRIMARY KEY CLUSTERED,
IsSelfJoin TINYINT NOT NULL,
HasPk TINYINT NOT NULL,
[SQL] NVARCHAR(4000) NOT NULL
)
DECLARE@Indent SMALLINT,
@RowID INT,
@ID INT,
@TSQL NVARCHAR(4000),
@RowSQL NVARCHAR(4000),
@EndSQL NVARCHAR(4000),
@pkColumn NVARCHAR(128),
@IsSelfJoin TINYINT,
@HasPk TINYINT
DECLARE@Unwind TABLE
(
RowID INT NOT NULL,
StepID INT IDENTITY(0, 1) PRIMARY KEY NONCLUSTERED,
[SQL] NVARCHAR(4000)
)
WHILE NOT EXISTS (SELECT * FROM @SQL WHERE RowID = 1)
BEGIN
SELECT TOP 1@RowID = c.RowID,
@ID = c.RowID,
@Indent = c.Indent,
@TSQL = N'',
@EndSQL = N'',
@IsSelfJoin = c.IsSelfjoin,
@HasPk = c.HasPk
FROM@Constraints AS c
LEFT JOIN@SQL AS s ON s.RowID = c.RowID
WHEREs.RowID IS NULL
ORDER BYc.Indent DESC,
c.RowID DESC
WHILE @ID > 0
BEGIN
IF @Indent = 0
SELECT@RowSQL = N'DELETE t' + CAST(@RowID AS NVARCHAR(12)),
@RowSQL = @RowSQL + N' FROM ' + QUOTENAME(c.[Catalog]) + N'.' + QUOTENAME(c.[Schema]) + N'.' + QUOTENAME(c.[Table]) + N' AS t' + CAST(@ID AS NVARCHAR(12)),
@EndSQL = N' WHERE t' + CAST(@ID AS NVARCHAR(12)) + '.' + QUOTENAME(COALESCE(c.[Column], '%0')) + N' = ''%1''',
@IsSelfJoin = @IsSelfJoin | c.IsSelfJoin
FROM@Constraints AS c
WHEREc.RowID = @ID
ELSE
SELECT@RowSQL = N' INNER JOIN ' + QUOTENAME(c.[Catalog]) + N'.' + QUOTENAME(c.[Schema]) + N'.' + QUOTENAME(c.[Table]),
@RowSQL = @RowSQL + N' AS t' + CAST(@ID AS NVARCHAR(12)) + N' ON t' + CAST(@ID AS NVARCHAR(12)) + N'.' + QUOTENAME(c.[Column]),
@pkColumn = QUOTENAME(c.pkColumn),
@IsSelfJoin = @IsSelfJoin | c.IsSelfJoin
FROM@Constraints AS c
WHEREc.RowID = @ID
SELECT TOP 1@ID = c.RowID,
@Indent = c.Indent,
@RowSQL = @RowSQL + N' = t' + CAST(c.RowID AS NVARCHAR(12)) + N'.' + @pkColumn,
@IsSelfJoin = @IsSelfJoin | c.IsSelfJoin
FROM@Constraints AS c
WHEREc.RowID < @ID
AND c.Indent < @Indent
ORDER BYc.Indent DESC,
c.RowID DESC
IF @@ROWCOUNT = 0
SET@ID = 0
SET@TSQL = @RowSQL + @TSQL
END
INSERT@SQL
(
RowID,
IsSelfJoin,
HasPk,
[SQL]
)
VALUES(
@RowID,
@IsSelfJoin,
@HasPk,
@TSQL + @EndSQL
)
IF @IsSelfJoin = 1
BEGIN
DECLARE@Yak NVARCHAR(160),
@Catalog NVARCHAR(128),
@Column NVARCHAR(128)
SELECT@Yak = pkType + COALESCE('(' + CAST(pkSize AS NVARCHAR(12)) + ')', ''),
@Catalog = [Catalog],
@Schema = [Schema],
@Table = [Table],
@Column = [Column],
@Catalog = [Catalog],
@Table = [Table],
@pkColumn = pkColumn
FROM@Constraints
WHERERowID = @RowID
SET@RowSQL = 'DECLARE@Lvl INT
SET@Lvl = 0
DECLARE@Stage TABLE (RowID INT IDENTITY(0, 1), Lvl INT, RowKey ' + @Yak + ')
INSERT @Stage (Lvl, RowKey) '
+ REPLACE(@TSQL + @EndSQL, 'DELETE t' + CAST(@RowID AS NVARCHAR(12)) + '', 'SELECT 0, t' + CAST(@RowID AS NVARCHAR(12)) + '.' + QUOTENAME(@Column) + '')
+ ' WHILE @@ROWCOUNT > 0
BEGIN
SET@Lvl = @Lvl + 1
INSERT@Stage (Lvl, RowKey)
SELECT@Lvl,
t.' + QUOTENAME(@pkColumn) + '
FROM' + QUOTENAME(@Catalog) + '.' + QUOTENAME(@Schema) + '.' + QUOTENAME(@Table) + ' AS t
INNER JOIN@Stage AS s ON s.RowKey = t.' + QUOTENAME(@Column) + '
AND s.Lvl = @Lvl - 1
LEFT JOIN@Stage AS cr ON cr.RowKey = t.' + QUOTENAME(@pkColumn) + '
WHEREcr.RowKey IS NULL
END
SELECT ''DELETE FROM ' + QUOTENAME(@Catalog) + '.' + QUOTENAME(@Schema) + '.' + QUOTENAME(@Table) + ' WHERE ' + QUOTENAME(@pkColumn) + ' = '' + QUOTENAME(RowKey, '''''''')
FROM @Stage
WHERE RowID > 0
ORDER BY RowID DESC'
INSERT@Unwind
(
RowID,
[SQL]
)
VALUES(
@RowID,
@RowSQL
)
END
END
INSERT@Return
(
RowID,
IsSelfJoin,
HasPk,
[SQL]
)
SELECTs.ID,
s.IsSelfJoin,
s.HasPk,
CASE
WHEN u.RowID IS NULL THEN s.[SQL]
ELSE u.[SQL]
END
FROM@SQL AS s
LEFT JOIN@Unwind AS u ON u.RowID = s.RowID
ORDER BYs.ID,
u.StepID
RETURN
ENDE 12°55'05.25"
N 56°04'39.16"
View 16 Replies
View Related
Mar 6, 2007
Hi All,I have a Access front-end,the tables are from SQL server 2000 andlinked via ODBC using DSN.I have a Main table and several related tables.The Main table has aOne -to- Many relationship with the related tables.When I try to delete a record from the front-end,I get a error msg "the table is locked,you and another user are attempting to delete/update the same record".I am the single user and I am at a loss tounderstand what the problem is.I have to explain that I never had a problem when i was using Accessas the backend too.Is it because of linking?Any suggestions?Roy
View 2 Replies
View Related
Jul 11, 2006
I need to update rows in database A from data in database B and delete from database B if a match is found but leave in databse B if no match found. Is there a way do do this with the OLe DB command?
View 1 Replies
View Related
Dec 13, 2007
Hi All,
I have this project I'm working on it's Product Content Management System rewrite. I got to the point of updating the Product By Sku and not sure if I should use UPDATE statement or I should DELETE sections assosiated with the ProductContentID and then re-insert them again? I'm not sure which is more afficient?
I can really do both and it's really not that complicated, the only problem I see with DELETE then INSERT is the ProductContentSectionId in the Sections table is going to grow very fast and I'm a bit concerned about it.
We use SQL Server 2000 and we have about 4 bound tables where we keep the data. The one I'm talking about is the sections table where we keep the actual types of product content like a BoxShot, Description, Key Features and so on...
Thank you in advance!
Tatyana Hughes
View 3 Replies
View Related
Mar 21, 2008
I have two buttons on my page, one which will update content the other which will delete content from my slq database.
I get this error "CS1501: No overload for method 'updateBlockContent' takes '2' arguments" when I click on my update button.
I get this error "CS1501: No overload for method 'deleteBlockContent' takes '2' arguments" when I click on my delete button.
This is my method for updating - UPDATE [CMS] SET [BlockName] = @BlockName, [BlockContent] = @BlockContent WHERE (([BlockID] = @Original_BlockID))
This is my C# code behind the update button:protected void btnUpdateBlock_Click(object sender, EventArgs e)
{CMSTableAdapter ta = new CMSTableAdapter();
try
{
ta.updateBlockContent(txtBlockName.Text, txtBlockContent.Text);lblFeedback.Text = "Block Updated.";txtBlockName.Text = txtBlockContent.Text = "";
}
catch (Exception ex) { lblFeedback.Text = ex.ToString(); }
finally
{lblFeedback.Visible = true;
ta.Dispose();
}
}
This is my method for deleting - DELETE FROM [CMS] WHERE (([BlockID] = @Original_BlockID))
This is my C# code behind the delete button:protected void btnDeleteBlock_Click(object sender, EventArgs e)
{CMSTableAdapter ta = new CMSTableAdapter();
try
{
ta.deleteBlockContent(txtBlockName.Text, txtBlockContent.Text);lblFeedback.Text = "Block Deleted.";txtBlockName.Text = txtBlockContent.Text = "";
}
catch (Exception ex) { lblFeedback.Text = ex.ToString(); }
finally
{lblFeedback.Visible = true;
ta.Dispose();
}
}
Can somebody please explain why this is happening and how to remedy this problem.
Thanks,
Chris.
View 5 Replies
View Related
Jul 12, 2004
hi,
I have a dataadaptor which i use to fill a dataset. I can do this no problems. But if I delete a row from the dataset and update the adaptor again, nothing gets changed in my source database. Does anyone know why this is?
here's my code for my dataadaptor:
SqlConnection conn = new SqlConnection(connectionString);
SqlDataAdapter adapter = new SqlDataAdapter();
adapter.SelectCommand = new SqlCommand("select * from tblExtRef", conn);
adapter.SelectCommand.Connection = conn;
SqlParameter parm;
SqlCommand cmd;
cmd = new SqlCommand("DELETE FROM tblextref WHERE extrefid = @extrefid",conn);
parm = cmd.Parameters.Add("@extrefid", SqlDbType.Int , 40, "extrefid");
parm.SourceColumn = "ExtRefid";
//parm.SourceVersion = DataRowVersion.Original;
//parm.Direction = ParameterDirection.InputOutput;
//SourceVersion = DataRowVersion.Original;
adapter.DeleteCommand = cmd;
View 1 Replies
View Related
Jul 23, 1999
Is it possible to cascade update and delete while DRI remains there ? That
is without deleteing refrential integrity. If anyone have example of cascade delete and Update using Pubs or Northwind Example , i'll be really obliged.
View 1 Replies
View Related
Jan 8, 1999
I am trying to update a SQL database with data from a Wang system. The Wang data is dumped to a txt file. I then import it into an update table in SQL via Access. Some of the data is new and some of the data is updated records. At this point I have been trying to create a script to update and add data to a table via the query tool in SQL Then delete data from the update table.
I was able to get the UPDATE and DELETE to work but I have not figured out how to insert new records at the same time? Can I use an IF statement? I will apreciate any help or sample code, Thanks.
UPDATE MemberList
Set Name = NameUpd, Address1 = Address1Upd, Address2 = Address2Upd, City = CityUpd, State = StateUpd, ZipCode = ZipCodeUpd, MemberStatus = MemberstatusUpd
FROM MemberList, MemberListUpd
WHERE MemberList.MemberNumber = MemberListUpd.MemberNumberUpd
INSERT ?
DELETE MemberListUpd
View 2 Replies
View Related
Feb 16, 2007
I know how to do this in MySQL; but I was hoping there was a way to do it in MS SQL.
I want to be able to limit the number of rows an UPDATE or DELETE will effect regardless of the WHERE clause. I want to do this as a stop gap in the event that there is a logical error in the WHERE clause that would make it effect more rows than is humanly intended.
If I write a complex query that I know should drill down to only affecting one row, I just want to lock it in before I run it on database and take the risk of damaging some data.
In MySQL you would just do something like this:
Code:
DELETE FROM table_name WHERE ... LIMIT 1;
The only thing close to LIMIT I could come up with for MS SQL was TOP but it seems to only work for SELECT.
Any ideas would be greatly appreciated.
I ask this because if you screw up; manually saving 1, 2 or even 5 rows is a lot easier than having to rescue a whole table of data (even if it on a development server).
View 2 Replies
View Related
May 11, 2007
Hi,
I have two tables:
1. RubricReport
2. RubricReportDetail
How can I code this step in my stored procedure:
If @ReportID is NULL, insert a row into RubricReport table, and set @ReportID=@@IDENTITY; otherwise, update table RubricReport for columns LastUpdate and LastUpdateBy, and delete table RubricReportDetail where ReportID=@ReportID.
Table RubricReport has columns ReportID, County,Dsitrict, DataYears, LastUpdate and LastUpdateBy
Table RubricReportDetail has columns ReportID, IndicatorID, LocalPerf
Kindly help me.
Thanks in advance
View 3 Replies
View Related
Jan 22, 2008
Im trying to keep a mirror image of some data Im getting from Quickbooks.
As the records are inserted into the database I need to check if a record exists and either update or insert a new one.
it seems easier just to delete using the tnxid and reinsert vs updating
my question is if I go
begin
INSERT INTO QBInvoicesQue(100s of feilds)
end
begin
delete from QBInvoices where txnid = @TxnID
end
and there is not matching txnid to delete from will it cause any problems? before going to the insert statement?
begin
INSERT INTO QBInvoices(100s of feilds)
end
View 6 Replies
View Related
Jul 23, 2005
Asking for some sample SQL/SP code to perform updates/deletes with joinedtables.Example 1:For every record that is matched on both tables A and B, update a field intable A.Example 2:For every record that is matched on both tables A and B, delete record intable A.TIA.~ Duane Phillips.
View 2 Replies
View Related
Jul 23, 2005
Hi all!Are there any other way than using rights or Triggers to prevent aDELETE or an UPDATE on a specific column.The "problem" with rights is that they dont apply to all DB-usersThe "problem" with triggers is that they generate lots of extraSQL-codeI would like a solution something like below. If there are anyprimitives like this or other more neat solutions I would be glad toknowCREATE TABLE some_table NO DELETE/* ^^^^^^^^^*/(some_column SOME_TYPE NO UPDATE/* ^^^^^^^^^*/)For clarity, here is a trigger that currently solves the problemCREATE TRIGGER check_updateable_columns ON some_tableFOR UPDATEASIF UPDATE(some_column)RAISERROR(...)GOorCREATE TRIGGER delete_not_allowed ON some_tableINSTEAD OF DELETEASRAISERROR(...)GO
View 1 Replies
View Related
Oct 16, 2007
I vaguely recall reading an article that I can no longer find that an update statement is executed as a combination of a Delete and an Insert by SQL server. Does anyone know if this a still a true statement in SQL Server 2005?
Thanks,
-shl
View 10 Replies
View Related
Aug 3, 2006
I'm referring to this bug, which I've seen a lot of people (including me) run into:
http://connect.microsoft.com/VisualStudio/feedback/ViewFeedback.aspx?FeedbackID=93937
Has anyone heard if MS plans to fix this soon?
Thanks
View 3 Replies
View Related
Sep 28, 2006
HelloI have a Trigger on a table. Here is the code ALTER TRIGGER [dbo].[OnOrderDelete]
ON [dbo].[orders]
AFTER DELETE,UPDATE
AS
BEGIN
SET NOCOUNT ON;
DECLARE @ids int;
SELECT @ids =( SELECT id from DELETED);
DELETE FROM files WHERE OrderId = @ids;
ENDActually the UPDATE event handler is not wanted here, but why when I leave him I have a following behaviour:When orders table is updated, the "SELECT @ids =( SELECT id from DELETED);
DELETE FROM files WHERE OrderId = @ids;" part is executed, and the program recognizes DELETED as UPDATED! (Like " SELECT @ids =( SELECT id from UPDATED) ") Is this right? And how can I part UPDATED and DELETED ?ThanksArtashes
View 12 Replies
View Related
Nov 15, 2006
I have had this problem before but it turned out to be dodgy SQL created by the wizard. Doesn't seem to be the case this time.The following does a postback but makes no changes. 1 <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:ehlConnectionString %>"
2 DeleteCommand="DELETE FROM [tblSubRegions] WHERE [SubRegionID] = ?"
3 InsertCommand="INSERT INTO [tblSubRegions] ([SubRegionID], [RegionID], [SubRegionName]) VALUES (?, ?, ?)"
4 ProviderName="<%$ ConnectionStrings:ehlConnectionString.ProviderName %>"
5 SelectCommand="SELECT tblSubRegions.SubRegionID, tblSubRegions.RegionID, tblSubRegions.SubRegionName, tblRegions.RegionName FROM (tblSubRegions INNER JOIN tblRegions ON tblSubRegions.RegionID = tblRegions.RegionID) WHERE (tblSubRegions.RegionID = ?) ORDER BY tblSubRegions.SubRegionName"
6 UpdateCommand="UPDATE [tblSubRegions] SET [RegionID] = ?, [SubRegionName] = ? WHERE [SubRegionID] = ?">
7
8 <DeleteParameters>
9 <asp:Parameter Name="SubRegionID" Type="Int32" />
10 </DeleteParameters>
11
12 <UpdateParameters>
13 <asp:Parameter Name="RegionID" Type="Int32" />
14 <asp:Parameter Name="SubRegionName" Type="String" />
15 <asp:Parameter Name="SubRegionID" Type="Int32" />
16 </UpdateParameters>
17
18 <SelectParameters>
19 <asp:ControlParameter ControlID="dropRegions" Name="RegionID" PropertyName="SelectedValue" Type="Int32" />
20 </SelectParameters>
21
22 <InsertParameters>
23 <asp:Parameter Name="SubRegionID" Type="Int32" />
24 <asp:Parameter Name="RegionID" Type="Int32" />
25 <asp:Parameter Name="SubRegionName" Type="String" />
26 </InsertParameters>
27
28 </asp:SqlDataSource>
29
30
31
32 <asp:SqlDataSource ID="SqlDataSource2" runat="server" ConnectionString="<%$ ConnectionStrings:ehlConnectionString %>"
33 ProviderName="<%$ ConnectionStrings:ehlConnectionString.ProviderName %>"
34 SelectCommand="SELECT [RegionID], [RegionName] FROM [tblRegions]">
35
36 </asp:SqlDataSource>
37
38
39
40 <asp:DropDownList id="dropStates" runat="server" OnSelectedIndexChanged="dropStates_SelectedIndexChanged" AutoPostBack="True">
41 </asp:DropDownList>
42
43 <asp:DropDownList id="dropRegions" runat="server" OnSelectedIndexChanged="dropRegions_SelectedIndexChanged" AutoPostBack="True">
44 </asp:DropDownList>
45
46
47
48 <asp:GridView ID="GridView1" runat="server" AllowPaging="True" AllowSorting="True"
49 AutoGenerateColumns="False" EnableViewState=false Width="100%" DataSourceID="SqlDataSource1">
50 <Columns>
51 <asp:TemplateField HeaderText="SubRegionName" SortExpression="SubRegionName">
52 <EditItemTemplate>
53 <asp:DropDownList ID="DropDownList1" runat="server" DataSourceID="SqlDataSource2"
54 DataTextField="RegionName" DataValueField="RegionID" SelectedValue='<%# Bind("RegionID") %>'>
55 </asp:DropDownList>
56 </EditItemTemplate>
57 <ItemTemplate>
58 <asp:Label ID="Label1" runat="server" Text='<%# Bind("SubRegionName") %>'></asp:Label>
59 </ItemTemplate>
60 </asp:TemplateField>
61 <asp:BoundField DataField="RegionName" HeaderText="RegionName" SortExpression="RegionName" />
62 <asp:CommandField ShowDeleteButton="True" ShowEditButton="True" />
63 </Columns>
64 </asp:GridView>
Thanks in advance. Shaun
View 1 Replies
View Related
Jan 15, 2007
Hi, I have a database which saves data about bus links. I want to provide a information to passenger about price of their journay. The price depends on three factors: starting busstop, ending busstop and type of ticket (full, part - for students and old people, ...).
So I created a table with three foreign key constraints (two for busstops and one for type).
When the busstop is deleted or type of ticket I want all data connected with it to be deleted automatically. I wanted to use cascade deleting.
But I receive a following exception: Introducing FOREIGN KEY constraint 'FK_TicketPrices_BusStops1' on table 'TicketPrices' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints.
How can I achieve my task? Why should it cause cycles or multiple cascade paths?
View 1 Replies
View Related
Jul 24, 2007
for now, doing a small school project, i find doing SPs for Insert useful, like checking for existing data and not inserting, that might not be the best method, i had advice from here i can use unique constraints instead, then what about update and delete? SPs also? the pros make SPs for everything? currently use dynamically generated SQL from SqlDataSources. for Update / delete. some delete are SPs too...
View 2 Replies
View Related
Oct 7, 2007
I'm working on a address book where customers can add, edit and delete address book entries. For the life of me I can't figure out what I'm messing up with the Update and Delete statements of this feature. Can someone please help me.Here's my code: <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConflictDetection="CompareAllValues" ConnectionString="<%$ ConnectionStrings:kalistadbConnectionString %>" DeleteCommand="DELETE FROM [Address] WHERE [AddID] = @original_AddID AND [AddNick] = @original_AddNick AND [AddFN] = @original_AddFN AND [AddLN] = @original_AddLN AND [AddCompany] = @original_AddCompany AND [AddAddress] = @original_AddAddress AND [AddCity] = @original_AddCity AND [AddProv_State] = @original_AddProv_State AND [AddPostal_Zip] = @original_AddPostal_Zip AND [AddCountry] = @original_AddCountry AND [AddPhone] = @original_AddPhone AND [CustID] = @original_CustID" InsertCommand="INSERT INTO [Address] ([AddNick], [AddFN], [AddLN], [AddCompany], [AddAddress], [AddCity], [AddProv_State], [AddPostal_Zip], [AddCountry], [AddPhone], [CustID]) VALUES (@AddNick, @AddFN, @AddLN, @AddCompany, @AddAddress, @AddCity, @AddProv_State, @AddPostal_Zip, @AddCountry, @AddPhone, @CustID)" OldValuesParameterFormatString="original_{0}" onselecting="SqlDataSource1_Selecting" OnInserting="SqlDataSource1_Inserting" SelectCommand="SELECT * FROM [Address] WHERE ([CustID] = @CustID)" UpdateCommand="UPDATE [Address] SET [AddNick] = @AddNick, [AddFN] = @AddFN, [AddLN] = @AddLN, [AddCompany] = @AddCompany, [AddAddress] = @AddAddress, [AddCity] = @AddCity, [AddProv_State] = @AddProv_State, [AddPostal_Zip] = @AddPostal_Zip, [AddCountry] = @AddCountry, [AddPhone] = @AddPhone, [CustID] = @CustID WHERE [AddID] = @original_AddID AND [AddNick] = @original_AddNick AND [AddFN] = @original_AddFN AND [AddLN] = @original_AddLN AND [AddCompany] = @original_AddCompany AND [AddAddress] = @original_AddAddress AND [AddCity] = @original_AddCity AND [AddProv_State] = @original_AddProv_State AND [AddPostal_Zip] = @original_AddPostal_Zip AND [AddCountry] = @original_AddCountry AND [AddPhone] = @original_AddPhone AND [CustID] = @original_CustID"> <SelectParameters> <asp:Parameter Name="CustID" /> </SelectParameters> <DeleteParameters> <asp:Parameter Name="original_AddID" Type="Int64" /> <asp:Parameter Name="original_AddNick" Type="String" /> <asp:Parameter Name="original_AddFN" Type="String" /> <asp:Parameter Name="original_AddLN" Type="String" /> <asp:Parameter Name="original_AddCompany" Type="String" /> <asp:Parameter Name="original_AddAddress" Type="String" /> <asp:Parameter Name="original_AddCity" Type="String" /> <asp:Parameter Name="original_AddProv_State" Type="String" /> <asp:Parameter Name="original_AddPostal_Zip" Type="String" /> <asp:Parameter Name="original_AddCountry" Type="String" /> <asp:Parameter Name="original_AddPhone" Type="String" /> <asp:Parameter Name="original_CustID" /> </DeleteParameters> <UpdateParameters> <asp:Parameter Name="AddNick" Type="String" /> <asp:Parameter Name="AddFN" Type="String" /> <asp:Parameter Name="AddLN" Type="String" /> <asp:Parameter Name="AddCompany" Type="String" /> <asp:Parameter Name="AddAddress" Type="String" /> <asp:Parameter Name="AddCity" Type="String" /> <asp:Parameter Name="AddProv_State" Type="String" /> <asp:Parameter Name="AddPostal_Zip" Type="String" /> <asp:Parameter Name="AddCountry" Type="String" /> <asp:Parameter Name="AddPhone" Type="String" /> <asp:Parameter Name="CustID" /> <asp:Parameter Name="original_AddID" Type="Int64" /> <asp:Parameter Name="original_AddNick" Type="String" /> <asp:Parameter Name="original_AddFN" Type="String" /> <asp:Parameter Name="original_AddLN" Type="String" /> <asp:Parameter Name="original_AddCompany" Type="String" /> <asp:Parameter Name="original_AddAddress" Type="String" /> <asp:Parameter Name="original_AddCity" Type="String" /> <asp:Parameter Name="original_AddProv_State" Type="String" /> <asp:Parameter Name="original_AddPostal_Zip" Type="String" /> <asp:Parameter Name="original_AddCountry" Type="String" /> <asp:Parameter Name="original_AddPhone" Type="String" /> <asp:Parameter Name="original_CustID" /> </UpdateParameters> <InsertParameters> <asp:Parameter Name="AddNick" Type="String" /> <asp:Parameter Name="AddFN" Type="String" /> <asp:Parameter Name="AddLN" Type="String" /> <asp:Parameter Name="AddCompany" Type="String" /> <asp:Parameter Name="AddAddress" Type="String" /> <asp:Parameter Name="AddCity" Type="String" /> <asp:Parameter Name="AddProv_State" Type="String" /> <asp:Parameter Name="AddPostal_Zip" Type="String" /> <asp:Parameter Name="AddCountry" Type="String" /> <asp:Parameter Name="AddPhone" Type="String" /> <asp:Parameter Name="CustID" /> </InsertParameters> </asp:SqlDataSource>
View 2 Replies
View Related
Oct 20, 2007
I have recently started an ASP.Net application and am having some issues updating, inserting and deleting rows. When I started working with it, I was getting errors because it could not find any update command. Eventually, I figured out how to automatically generate the commands, by configuring my SQLDataSource control and clicking the "advanced" button. Right now though, I have generated the commands, but I still can not insert, update or delete rows. When I attempt to update anything, I recieve an error that says "The data types text and nvarchar are incompatible in the equal to operator." Nowhere in my table do I have any rows that use the datatype "nvarchar", only "text" and "int". I tried switching all of my text columns to "nvarchar(500)", which did not help. I am led to believe that the auto generated SQL procedures are trying to do something behind the scenes that is making my database act up, because even when I delete rows, I get the same exception, so the datatypes cannot be messed up there, because all that the datasource is doing is deleting rows, therefore there is no need to worry about data types. I only get the error when I check the "Use optimistic concurrency" box. When I do not use optimistic concurrency, I can delete, insert, and update rows... but nothing happens. There are no errors, but nothing is deleted, updated or inserted either. Upon postback, nothing has changed. I may upload a copy of the exact exception page, if someone thinks that it may help. Here is the update command that was generated: UPDATE [Record Information] SET [Speed] = @Speed, [Recording Company] = @Recording_Company, [Year] = @Year, [Artist] = @Artist, [Side 1 Track Title] = @Side_1_Track_Title, [Side 1 Track Duration] = @Side_1_Track_Duration, [Side 2 Track Title] = @Side_2_Track_Title, [Side 2 Track Duration] = @Side_2_Track_Duration, [Sleeve Description] = @Sleeve_Description WHERE [Record Database ID] = @original_Record_Database_ID
Apparently no stored procedures exist for any of these operations, and I am unsure why. The "Record Database ID" is my identity column, and is the only field that is (and is supposed to be) uneditable.
View 1 Replies
View Related
Jan 2, 2008
Hi all,Happy New Year!I've just install VS .NET 2005 and try to play with Gridviewwhen I configure the datasource for Gridview and click "Advance" in order to enable Update, Delete Select etc...the checkBox is not selectableCan someone pls show me how?Thanks in advance.
View 2 Replies
View Related
Nov 13, 2005
Hi,I just upgraded my ASP.NET 2.0 BETA 2 environment to the final release of ASP.NET 2.0 VWD.Once the update was finished, I could open my website without any problems..... Now, I noticed that in the final release, some modifications have been included in the Membership Stored Procedure and other stored procedures. So I created a new database (SQL Express) and added my data again.After re-creating my SQLDataSources, I tryed to enable the Editing and Deleting option in VWD and once I run my web application, it seems when selecting editing and then update, it doesn't work anymore....This is my code :
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:IMMOASPNETDBConnectionString %>"
DeleteCommand="DELETE FROM aspnet_test WHERE (testID = @Original_testID)" SelectCommand="SELECT BuyID, BuyNL, BuyFR, Lastupdated FROM aspnet_Buy"
UpdateCommand="UPDATE aspnet_Buy SET BuyNL = @BuyNL, BuyFR = @BuyFR WHERE (BuyNL = @original_BuyID)">
<DeleteParameters>
<asp:Parameter Name="Original_testID" />
</DeleteParameters>
<UpdateParameters>
<asp:Parameter Name="BuyNL" />
<asp:Parameter Name="BuyFR" />
<asp:Parameter Name="original_BuyID" />
</UpdateParameters>
</asp:SqlDataSource>
<br />
<br />
<asp:GridView ID="GridView1" runat="server" AllowPaging="True" AutoGenerateColumns="False"
DataKeyNames="BuyID" DataSourceID="SqlDataSource1">
<Columns>
<asp:CommandField ShowEditButton="True" />
<asp:BoundField DataField="BuyID" HeaderText="BuyID" InsertVisible="False" ReadOnly="True"
SortExpression="BuyID" />
<asp:BoundField DataField="BuyNL" HeaderText="BuyNL" SortExpression="BuyNL" />
<asp:BoundField DataField="BuyFR" HeaderText="BuyFR" SortExpression="BuyFR" />
<asp:BoundField DataField="Lastupdated" HeaderText="Lastupdated" SortExpression="Lastupdated" />
</Columns>
</asp:GridView>Can someone help me with this ? What is wrong with the Update command ?Thanks to all,Bart
View 3 Replies
View Related
