How To Correctly Update A Table Which Values Can Be Either Inserted/updated/deleted On Update?
Feb 16, 2006
Hi SQL fans,
I realized that I often encounter the same situation in a relation
database context, where I really don't know what to do. Here is an
example, where I have 2 tables as follow:
Note that I also have a "Titles" tables (hence the tfolio_idtitle
link).
My problem is : When I update a portfolio, I must update all the
associated titles in it. That means that titles can be either removed
from the portfolio (a folio does not support the title anymore), added
to it (a new title is supported by the folio) or simply updated (a
title stays in the portfolio, but has its weight changed)
and I must update the PortfolioTitle based on these values :
idFolio | idTitre | poids
2 2 20
2 3 35
2 4 40
then I should
1 ) remove the title #1 from the folio by deleting its entry in the
PortfolioTitle table
2 ) update the title #2 (weight from 30 to 35)
3 ) add the title #4 to the folio
For now, the only way I've found to do this is delete all the entries
of the related folio (e.g.: DELETE TitrePortefeuille WHERE idFolio =
2), and then insert new values for each entry based on the new given
values.
Is there a way to better manage this by detecting which value has to be
inserted/updated/deleted?
on insert in a new table a have to change one column before insert.
I wrote this trigger:
create trigger SUBSCR_ID_TRANSFER ON dbo.SalesOrderExtensionBase AFTER INSERTAS BEGIN SET NOCOUNT ON;DECLARE @OpportunityID uniqueidentifier;DECLARE @subscrId uniqueidentifier;declare @salesorderid uniqueidentifier;set @salesorderid = (select SalesorderID from inserted)SET @OpportunityID = (SELECT OpportunityId FROM SalesOrderBase where SalesOrderID=@salesorderid)SET @subscrId = (SELECT New_old_subscridId from OpportunityExtensionbase where OpportunityID=@OpportunityID)Update inserted set New_old_subscridId = @subscrIdENDbut SQL Rise the error "The inserted values can not be modified"
HI, I am wondering if it is possible to retreive this information without using row count transform. Can I get the # of rows inserted/updated or deleted by destination from the log?
Stepping thru the code with the debugger shows the dataset rows being deleted.
After executing the code, and getting to the page presentation. Then I stop the debug and start the page creation process again ( Page_Load ). The database still has the original deleted dataset rows. Adding rows works, then updating works fine, but deleting rows, does not seem to work.
The dataset is configured to send the DataSet updates to the database. Use the standard wizard to create the dataSet.
cDependChildTA.Fill(cDependChildDs._ClientDependentChild, UserId); rowCountDb = cDependChildDs._ClientDependentChild.Count; for (row = 0; row < rowCountDb; row++) { dr_dependentChild = cDependChildDs._ClientDependentChild.Rows[0]; dr_dependentChild.Delete(); //cDependChildDs._ClientDependentChild.Rows.RemoveAt(0); //cDependChildDs._ClientDependentChild.Rows.Remove(0); /* update the Client Process Table Adapter*/ // cDependChildTA.Update(cDependChildDs._ClientDependentChild); // cDependChildTA.Update(cDependChildDs._ClientDependentChild); } /* zero rows in the DataSet at this point */ /* update the Child Table Adapter */ cDependChildTA.Update(cDependChildDs._ClientDependentChild);
When i debug a trigger is it possible to add a WATCHon the INSERTED or DELETED?I think not, at least I couldn't figure out a way to do so.Does someone have a suggestion on how I can see the values?I did try to do something likeINSERT INTO TABLE1(NAME)SELECT NAME FROM INSERTEDbut this didn't work. When the trigger completed and Iwent to see the TABLE1, there were no records in it.Are there any documents, web links that describe waysof debugging the trigger's INSERTED and DELETED?Thank you
I am trying to update one table when records are inserted in another table.
I have added the following trigger to the table “ProdTr” and every time a record is added I want to update the field “Qty3” in the table “ActInf” with a value from the inserted record.
My problem appears to be that I am unable to fill the variables with values, and I cannot understand why it isn’t working, my code is:
ALTER trigger [dbo].[antall_liter] on [dbo].[ProdTr] for insert as begin declare @liter as decimal(28,6)
I would like to access the data inserted or deleted within a trigger. however the built-in tables -- inserted and deleted --- are not accessible. anyone knows why? And is there any other way to do this?
I am having a hard time understanding triggers. My goal is to put a trigger on table x where records are UPDATED or DELETED. When this trigger fires I need to take the record ID and put the ID modified record into table y with the date modified. so basically logging the recordid changed with the getDate()
I don't quite understand how to get the rowid of the modified record.
I have a stored procedure that's running a little slower than I would like. I've executed the stored proc in QA and looked at the execution plan and it looks like the problem is in a trigger on one of the updated tables. The update on this table is affecting one row (I've specified the entire unique primary key, so I know this to be the case). Within my trigger there is some code to save an audit trail of the data. One of these statements does an update of the history table based on the inserted and deleted tables. For some reason this is taking 11.89% of the batch cost (MUCH more than any other statement) and within this statement 50% of the cost is for a table scan on inserted and 50% is for a table scan on deleted. These pseudo-tables should only contain one record each though.
Any ideas why this would be causing such a problem? I've included a simplified version of the update below. The "or" statements actually continue for all columns in the table. The same trigger template is used for all tables in the database and none of the others seem to exhibit this behavior as far as I can tell.
Thanks for any help! -Tom.
UPDATE H_MyTable SET HIST_END_DT = @tran_date FROM H_MyTable his INNER JOIN deleted del ON (his.PrimaryKey1 = del.PrimaryKey1) and (his.PrimaryKey2 = del.PrimaryKey2) INNER JOIN inserted ins ON (his.PrimaryKey1 = ins.PrimaryKey1) and (his.PrimaryKey2 = ins.PrimaryKey2) WHERE (his.HIST_END_DT is null) and ((IsNull(del.PrimaryKey1, -918273645) <> IsNull(ins.PrimaryKey1, -918273645)) or (IsNull(del.PrimaryKey2, -918273645) <> IsNull(ins.PrimaryKey2, -918273645)) or (IsNull(del.Column3, -918273645) <> IsNull(ins.Column3, -918273645)))
Is there a configuration or a trick to improve the speed of the access to inserted and deleted tables whithin a trigger? Whenever a trigger is called, the access to inserted or deleted constitute approximatly 95% of the execution time.
Is there a way to have access to inserted and to deleted improved other than copying the data to another table?
Hi guys, may I know is it possible to create an update trigger like this ? Assuming there are two database, database A and database B and both are having same tables called 'Payments' table. I would like to update the Payments records on database A automatically after Payments records on database B had been updated. I can't use replication because both tables might having different records and some records are the same. Hope can get any assistance here, thank you.
table2 is intially populated (basically this will serve as historical table for view); temptable and table2 will are similar except that table2 has two extra columns which are insertdt and updatedt
process: 1. get data from an existing view and insert in temptable 2. truncate/delete contents of table1 3. insert data in table1 by comparing temptable vs table2 (values that exists in temptable but not in table2 will be inserted) 4. insert data in table2 which are not yet present (comparing ID in t2 and temptable) 5. UPDATE table2 whose field/column VALUE is not equal with temptable. (meaning UNMATCHED VALUE)
* for #5 if a value from table2 (historical table) has changed compared to temptable (new result of view) this must be updated as well as the updateddt field value.
I'm using a performance monitor counter with an alert to catch merge replication conflicts. As you might have seen in my earlier psting this alert keeps running even if all conflicts have been resolved. After almost 2 days searching I found that in the table master..sysperfinfo there all the PM counters are stored still has the value 4 for conflicts/sec. When I manually create a new conflict this value becomes 5 which is nonsense since it's not the number of conflicts per second but the total number of conflicts since I started the server. If I check in PM it shows the correct value, whih of course is zero 99% of the time. Has anybody ever experienced the same kind of problem??
Or does anybody know a way of resetting the values in sysperfinfo. Even after allowing direct updates to systemtables I still can't modify the table.
I am creating update trigger(After) on a table.I want to catch an updated row and insert the values of this row into a new table.How can i catch this row while creating a trigger after update.
I want to update several columns in different tables whenever an update is happend on specific table. I have a trigger for that which update all the relevant places when it fires.
I want to do this update only if certains columns were changed, otherwise - do anything, to reduce performance.
How Can I know which columns were updated inside the trigger? The tables has many columns and only if 2 of them were changed then I need to update other tables.
I wrote a stored proc to be implemented in a datagrid. I went and used it on a record to update some information and it updated THE WHOLE DATABASE WITH THAT ONE RECORD..
IF anyone could shead some light on what I'm doing wrong that would be great.
Using MS SQL 2000I have a stored procedure that processes an XML file generated from anAudit program. The XML looks somewhat like this:<ComputerScan><scanheader><ScanDate>somedate&time</ScanDate><UniqueID>MAC address</UniqueID></scanheader><computer><ComputerName>RyanPC</ComputerName></computer><scans><scan ID = "1.0" Section= "Basic Overview"><scanattributes><scanattribute ID="1.0.0.0" ParentID=""Name="NetworkDomian">MSHOMe</scanattribute>scanattribute ID = "1.0.0.0.0" ParentID="1.0.0.0", etc etc....This is the Update portion of the sproc....CREATE PROCEDURE csTest.StoredProcedure1 (@doc ntext)ASDECLARE @iTree intDECLARE @assetid intDECLARE @scanid intDECLARE @MAC nvarchar(50)CREATE TABLE #temp (ID nvarchar(50), ParentID nvarchar(50), Namenvarchar(50), scanattribute nvarchar(50))/* SET NOCOUNT ON */EXEC sp_xml_preparedocument @iTree OUTPUT, @docINSERT INTO #tempSELECT * FROM openxml(@iTree,'ComputerScan/scans/scan/scanattributes/scanattribute', 1)WITH(ID nvarchar(50) './@ID',ParentID nvarchar(50) './@ParentID',Name nvarchar(50) './@Name',scanattribute nvarchar(50) '.')SET @MAC = (select UniqueID from openxml(@iTree, 'ComputerScan',1)with(UniqueID nvarchar(30) 'scanheader/UniqueID'))IF EXISTS(select MAC from tblAsset where MAC = @MAC)BEGINUPDATE tblAsset set DatelastScanned = (select ScanDate fromopenxml(@iTree, 'ComputerScan', 1)with(ScanDate smalldatetime'scanheader/ScanDate')),LastModified = getdate() where MAC =@MACUPDATE tblScan set ScanDate = (select ScanDate fromopenxml(@iTree,'ComputerScan', 1)with(ScanDate smalldatetime 'scanheader/ScanDate')),LastModified = getdate() where MAC =@MACUPDATE tblScanDetail set GUIID = #temp.ID, GUIParentID =#temp.ParentID, AttributeValue = #temp.scanattribute, LastModified =getdate()FROM tblScanDetail INNER JOIN #tempON (tblScanDetail.GUIID = #temp.ID ANDtblScanDetail.GUIParentID =#temp.ParentID AND tblScanDetail.AttributeValue = #temp.scanattribute)WHERE MAC = @MAC!!!!!!!!!!!!!!!!!! THIS IS WHERE IT SCREWS UP, THIS NEXT INSERTSTATEMENT IS SUPPOSE TO HANDLE attributes THAT WERE NOT IN THE PREVIOUSSCAN SO CAN NOT BE UDPATED BECAUSE THEY DON'T EXISTYET!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!INSERT INTO tblScanDetail (MAC, GUIID, GUIParentID,ScanAttributeID,ScanID, AttributeValue, DateCreated, LastModified)SELECT @MAC, b.ID, b.ParentID,tblScanAttribute.ScanAttributeID,@scanid, b.scanattribute, DateCreated = getdate(), LastModified =getdate()FROM tblScanDetail LEFT OUTER JOIN #temp a ON(tblScanDetail.GUIID =a.ID AND tblScanDetail.GUIParentID = a.ParentID ANDtblScanDetail.AttributeValue = a.scanattribute), tblScanAttribute JOIN#temp b ON tblScanAttribute.Name = b.NameWHERE (tblScanDetail.GUIID IS NULL ANDtblScanDetail.GUIParentID ISNULL AND tblScanDetail.AttributeValue IS NULL)ENDELSEBEGINHere are a few table defintions to maybe help out a little too...if exists (select * from dbo.sysobjects where id =object_id(N'[dbo].[FK_tblScan_tblAsset]') and OBJECTPROPERTY(id,N'IsForeignKey') = 1)ALTER TABLE [dbo].[tblScan] DROP CONSTRAINT FK_tblScan_tblAssetGOif exists (select * from dbo.sysobjects where id =object_id(N'[dbo].[tblAsset]') and OBJECTPROPERTY(id, N'IsUserTable') =1)drop table [dbo].[tblAsset]GOCREATE TABLE [dbo].[tblAsset] ([AssetID] [int] IDENTITY (1, 1) NOT FOR REPLICATION NOT NULL ,[AssetName] [nvarchar] (50) COLLATESQL_Latin1_General_CP1_CI_AS NULL,[AssetTypeID] [int] NULL ,[MAC] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,[DatelastScanned] [smalldatetime] NULL ,[NextScanDate] [smalldatetime] NULL ,[DateCreated] [smalldatetime] NULL ,[LastModified] [smalldatetime] NULL ,[Deleted] [bit] NULL) ON [PRIMARY]GO-----------------------------if exists (select * from dbo.sysobjects where id =object_id(N'[dbo].[tblScan]') and OBJECTPROPERTY(id, N'IsUserTable') =1)drop table [dbo].[tblScan]GOCREATE TABLE [dbo].[tblScan] ([ScanID] [int] IDENTITY (1, 1) NOT FOR REPLICATION NOT NULL ,[AssetID] [int] NULL ,[ScanDate] [smalldatetime] NULL ,[AssetName] [nvarchar] (50) COLLATESQL_Latin1_General_CP1_CI_AS NULL,[MAC] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,[DateCreated] [smalldatetime] NULL ,[LastModified] [smalldatetime] NULL ,[Deleted] [bit] NOT NULL) ON [PRIMARY]GO----------------------------if exists (select * from dbo.sysobjects where id =object_id(N'[dbo].[tblScanDetail]') and OBJECTPROPERTY(id,N'IsUserTable') = 1)drop table [dbo].[tblScanDetail]GOCREATE TABLE [dbo].[tblScanDetail] ([ScanDetailID] [int] IDENTITY (1, 1) NOT FOR REPLICATION NOTNULL ,[ScanID] [int] NULL ,[ScanAttributeID] [int] NULL ,[MAC] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,[GUIID] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_ASNOT NULL,[GUIParentID] [nvarchar] (50) COLLATESQL_Latin1_General_CP1_CI_ASNULL ,[AttributeValue] [nvarchar] (50) COLLATESQL_Latin1_General_CP1_CI_ASNULL ,[DateCreated] [smalldatetime] NULL ,[LastModified] [smalldatetime] NULL ,[Deleted] [bit] NOT NULL) ON [PRIMARY]GO------------------------------------------------------------My problem is that Insert statement that follows the update intotblScanDetail, for some reason it just seems to insert everything twiceif the update is performed. Not sure what I did wrong but any helpwould be appreciated. Thanks in advance.
Im using a trigger to check updates on particular table and execute a email. it works but it doesnt show the right record im looking into one table called SiteInfo. here is my code Im using sql 2005, can someone look at my code or the select statement.
