Problem: Trigger And Multiple Updates To The Table
Apr 21, 2004
Hey, I have couple of triggers to one of the tables. It is failing if I update multiple records at the same time from the stored procedure.
UPDATE
table1
SET
col1 = 0
WHERE col2 between 10 and 20
Error I am getting is :
Server: Msg 512, Level 16, State 1, Procedure t_thickupdate, Line 12
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
The statement has been terminated.
What is the best possible way to make it work? Thank you.
I was able to catch one update but not multiple updates or batch updates done to the table. I know the updated records are residing in inserted and deleted tables. Without using cursors, how can i read and compare all the rows in these two tables?
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...
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?
I've a system of users and let's call em subusers. Every User becomes an automatic generated login when entered into the database. Every subuser has a reference to his user and no login, cause only thr root in the chain is able to login. But when the user gets deleted, all subusers become a new user. I've done this with a trigger changing the superUser Value=0:
create trigger abc on Users AFTER DELETE as declare @h int SELECT @h = id FOM inserted UPDATE Users SET superUser=0 WHERE superUser=@h
Furthermore the trigger deletes all additionally data of the user. Since every subuser of the deleted user becomes a user himself for every subuser a new Login must be created. I'm using an update triger for this task:
1: create trigger userUpdate on Users After Update 2: AS 3: DECLARE @superold int 4: @supernew int 5: @name nvarchar(55) 6: @date smalldatetime 7: if UPDATE(superUser) 8: begin 9: SELECT @superold= superUser FROM deleted 10: SELECT @supernew=superUser FROM inserted 11: 12: if @superold <> @supernew 13: begin 14: if @superold = 0 15: begin 16: DELETE FROM UserLogin WHERE id=@superold 17: end 18: else if @supernew=0 19: begin 20: SELECT @name=Name,@date=Date from inserted 21: execute createLogin @supernew,@name,@date 21: end 22: end 23: end
The problem is in line 20 and 21, cause the values @name and @date containing only the last updated user(the last entry in the inserted table) thus only for the last user a new Login is created whereas the others become the state user but no login was created. What i need is a method to loop over all entrys in the tables inserted btw. deleted. Does anybody know how to achieve this, looping the tables and executing a stored procedure for every entry?
Converting an existing application, I have a table:
create table problem (
building char(3), function char(4), sqft int, pct dec(5,2) )
[pct] is a problem because it depends on it's own row and the sum of all other rows for the same building:
pct = sqft / sum(sqft) over building
I want to create a trigger to update the pct column in the database instead of any business layer. But, because it's updating itself I will use an Instead Of trigger that is separate from the table's Insert & Delete triggers.
The table has a primary key defined such that 'In-Place' updates will be used, that was a technique for reducing disk activity way back when and I can find no reference to it in SQL2005 BOL.
My question is, does the 'Inserted' table still exist for 'In-Place' updates? Or more basically, does the In-Place update still exist?
I am trying to implement trigger which can handle multirow updates and which is running on replicated table. So I want it never fails as trigger failure brakes replication.
So:
CREATE TRIGGER on_person_update
BEGIN
-- create temp table
-- populate temp table with Inserted values (I do not need Deleted as PK never change)
COMMIT TRAN
-- Am I right that this insures updates on replicated table will never be rollback after this commit?
BEGIN TRAN A
-- Make a checkpoint here to be able to rollback at any time to this point if something wrong inside loop.
SAVE TRAN MyTran
-- Start looping in temp table
-- RUN DML statement to make neccesary changes for each record in temp table
-- Does it make any sense to do this (IF @ERR below)? When I am trying in DML insert string value into integer column it never gets to IF statement - terminates straight away.
-- Reason why I think I need it as this trigger might be called by another trigger and top level trigger will get an error and can make a decision based on this.
IF @ERR <> 0 BEGIN ROLLBACK TRAN MyTran RAISERROR('Insert or Update failed in on_person_sls_update trigger with error: %s', 16, 1, @ERR) RETURN END
-- End looping temp
-- Do I need here COMMIT TRAN A or trigger will make commit anyway?
END
Why all of this?
Data changed on distributor and arrive to subscriber as a transaction.
We have a trigger on replicated table which will update replicated table in any way but after that it will update another database on subscriber.
This trigger should be able to handle multirow updates.
When this trigger updates another database it runs DML which fires other triggers so they become nested, if I am right. Our trigger should always accept changes from distributor as if it fails replication brakes but after data saved in temp table none or all changes have to be made.
May be I am copmpletely wrong with this template - hope somebody will help.
Assuming I should be using values from temp inserted to insure correct record... Need help coding IF...THEN INSERT statements in following After TRIGGER:
Create TRIGGER trg_insertItemRows
ON dbo.a_form AFTER INSERT
AS SET NOCOUNT ON -- Checkbox Driven: IF a_form.missingCheckbox = -1 THEN Insert into b_items (form_ID, parent_ID, ItemTitle) Values (Select Distinct i.form_ID,i.parent_ID from inserted i)', '+ 'User checked Missing Data')
-- Textbox Driven: IF a_form.incorrectTxtbox <> 'na' THEN Insert into b_items (form_ID, parent_ID, ItemTitle) Values (Select Distinct i.form_ID,i.parent_ID from inserted i)', '+ Correction: Replace '+ incorrectTxtbox + ' with '+replaceWithTxtbox)
Sample code below:
-- Source table the Trigger acts on Create Table a_form ( form_ID int Not Null, parent_ID int, missingCheckbox bit, missingNote varchar(100), incorrectTxtbox varchar(50), replaceWithTxtbox varchar(50) )
--Target table Trigger inserts into Create Table b_items ( items_ID int Not Null, form_ID int Not Null, parent_ID int, ItemTitle varchar(150) )
I am looking to update a record from a previous row. So if there is a value of total goods in week 1, i want that value to carry forward to the value of goods in week 2. Is there any SQL as an example of the best way to accomplish this? I can query it using lag() which works great but i need the source data itself to update as the end-users are accessing the data via lightswitch, so when they save a change, i want the trigger (or whatever you recommend) to update the source table.
Hello, I am working on a web app and am at a point where I have multiple rows in my GUI that need to be sent to and saved in SQL Server when the user presses Save. We want to pass the rows to a working table and the do a begin tran, move rows from working table to permanent tables with set processing, commit tran. The debate we are having is how to get the data to the work table. We can do individual inserts to the work table 1 round trip for each row (could be 100's of rows) or concatenate all rows into 1 long (up to 8K at a time) string and make one call sending the long string and then parse it into the work table in SQL Server. Trying to consider network usage and overhead by sending many short items vs 1 long item and cpu overhead for many inserts vs string manipulation and parsing. Suggestions?
In the ECASE table there is trigger to get the max value of case_id column in ecase based on project and increment one to that case_id value and insert into ecase table .
When we insert a new record to the ECASE table this trigger calls and insert the case_id column value.
When i run with multiple threads , the transaction is rolled back because of trigger . The reason is , on the project table the lock is happening while getting the max value of case_id column based on project.
Is it possible to do an update on multiple records. I have fields CusOrderID and CusCreditAmt in table CusOrder; and fields CusOrderID and CreditAmt in table CusCredits. I would like to update the value in the CusCreditAmt field in CusOrder to equal the CreditAmt field in CusCredits where CusOrderID is the same in both tables.
I tried doing this in a stored procedure based on a View with an inner join between the 2 tables as follows:
Update View1 Set CusCreditAmt = CreditAmt
I received an error that the subquery returned more than one value. Is there anyway to do something like this and make it work?
Writing this code using a CTE or any other method which do not use a table variable or a temp table as the users do not permissions to run this code from the application because of their roles which are required for creating and dropping the temp tables. I replaced table variable in place of Temp tables however the query never completes, probably because of the number of rows.
SELECT A.[Entry No_], A.[G_L Account No_],A.[Gen_ Prod_ Posting Group],A.[Document No_],A.[Posting Date] INTO temp1 FROM [G_L Entry] as A ,[G_L Account] as B where A.[G_L Account No_]= B.[No_] SELECT VE.[Entry No_],VE.[Document No_],VE.[Posting Date],VE.[Gen_ Prod_ Posting Group] , GLILER.[G_L Entry No_] INTO temp2
Is there a way to update all of the records in a table all at once using the results of a select of a different table's data?
For example. There are two tables, each have a primary index of catnum. One table (invoice_items) contains the line items (catnum) of customer invoices. The other table (sales_history) is a sales history table. I want to select all data from the invoice_items and sum the sales for various time periods (sales_0to30days, sales_31to60days, etc.) I then want to update the sales_history table which has columns: catnum, sales_0to30, sales_31to60, etc.). I want to run this daily to update all records.
Using SQL2000, is there another way of optimizing the original query below.TIA,BobUpdate Transactionset field1 = (select (sum(tax)-sum(credit))/sum(credit) from tblOrder wheretblOrder.id = Transaction.id),field2 = (select avg(price) from tblOrder where tblOrder.id =Transaction.id),field3 = (select min(cost) from tblOrder where tblOrder.id = Transaction.id)etc..(there are six additional updates)Is this a quicker way:Update TransactionSET field1 = (sum(tax)-sum(credit))/sum(credit) , field2 = avg(price) ,field3 = min(cost) , etc.....FROM tblOrderWHERE tblOrder.id = Transaction.id
Howdy,I need to write an update query with multiple aggregate functions.Here is an example:UPDATE tSETt.a = ( select avg(f.q) from dbo.foo f where f.p = t.y ),t.b = ( select sum(f.q) from dbo.foo f where f.p = t.y )FROM dbo.test tBasically I need to get some aggregate statistics about the rows offoo and store them in rows of t. The above statement works fine...butnote how the two subSelect's have the exact same WHERE clause. Thisscreams at me to combine them...but how? I would like to havesomething like this in my query:SELECT avg(f.q), sum(f.q) FROM dbo.foo f WHERE f.p = 2...and somehow store the results in t.a and t.b. Is there any way todo this?Thanks before hand!
I have a table used by multiple applications. One column is an Identify field and is also used as a Primary key. What isare the best practices to use get the identity value returned after an INSERT made by my code.. I'm worried that if someone does an INSERT into the same table a "zillionth" of a second later than I did, that I could get their Identity value.
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?
I have a trigger set on TABLE1 so that any update to this column should set off trigger to write to the AUDIT log table, it works fine otherwise but not the very first time when table1 has null in the column. if i comment out
and i.req_fname <> d.req_fname from the where clause then it works fine the first time too. Seems like null value of the column is messing things up
Any thoughts?
Here is my t-sql
Insert into dbo.AUDIT (audit_req, audit_new_value, audit_field, audit_user)
select i.req_guid, i.req_fname, 'req_fname', IsNull(i.req_last_update_user,@default_user) as username from inserted i, deleted d
I have a stored procedure that creates a temporary table, and then populates it using an INSERT and then a series of UPDATE statements. The procedure then returns the temporary table which will contain data in all of its columns.
When I call this procedure from SSRS 2005, the rowset returned contains data in only those columns that are populated by the INSERT statement. The columns that are set via the UPDATE statements are all empty. I read (in the Hitchhikers Guide to Reporting Services) that SSRS will only process the first rowset in a stored procedure that generates multiple rowsets. Is this true? Is this why SSRS does not retrieve data for the columns that are populated by the UPDATE statements?
-- File: sp_GetProgramsWatchedByDateRange.sql -- Desc: Returns EDP program and related channel (i.e., provider) information from the IPTV Data warehouse. -- Note that some of that data used by this procedure are obtained from the RMS_EPG database -- which is created by an application (loadEPG) that loads the EPG data from a GLF format XML file. -- Auth: H Hunsaker -- Date: 11/07/2006
-- Example invocation -- EXEC dbo.sp_GetProgramsWatchedByDateRange ...
-- Arguments/Parameters:
-- Parameter Name Type Description -- 3. StartDate datetime First date of reporting period -- 4. EndDate datetime Last date of reporting period -- TerseMode bit Return all columns? (1 = no, 0 = yes) -- 5. AsXML bit Resultset format (0 = standard, 1 = XML) -- 6. Debug bit Debug mode (0 = off, 1 = on). Currently disabled
IF OBJECT_ID (N'dbo.sp_GetProgramsWatchedByDateRange') IS NOT NULL DROP PROCEDURE dbo.sp_GetProgramsWatchedByDateRange GO
CREATE PROCEDURE dbo.sp_GetProgramsWatchedByDateRange @StartDate datetime = NULL, @EndDate datetime = NULL, @TerseMode bit = 0, @AsXML bit = 0, @Debug bit = 0 AS -- Notes: Much of the program content (roles, flags, etc.) that we want is not stored in the IPTV data warehouse. -- So I am going to the RMS_EPG database to obtain that information. -- We will have to ensure that the 2 databases are generated at the same or a matching time -- in order to to ensure that all programID values in the data warehouse can be located in the RMS_EPG database.
-- Debug code for testing -- DECLARE @StartDate datetime -- DECLARE @EndDate datetime -- DECLARE @TerseMode bit
-- Basic program information tprogram int NULL, -- programID from EPG XML, needed to access program data in the RMS_EPG db. tprogramId uniqueidentifier NULL, -- programID generated by IPTV tprogramTitle varchar(150) NULL, tprogramEpisodeTitle varchar(100) NULL, tprogramDescription varchar(500) NULL,
-- Flags ClosedCaption bit NULL, InStereo bit NULL, Repeats bit NULL, New bit NULL, Live bit NULL, Taped bit NULL, Subtitled bit NULL, SAP bit NULL, ThreeD bit NULL, Letterbox bit NULL, HDTV bit NULL, Dolby bit NULL, DVS bit NULL,
-- I store the program watching data in a temp table because -- data from the VIL and the Sandbox that were used to test this procedure were either incomplete or invalid. -- Use of a temp table with a series of updates allow me more control over the result set.
IF @StartDate IS NOT NULL AND @EndDate IS NOT NULL INSERT INTO #programWatched ( tdeviceId, tprogramId, --tprogramTitle, --tprogramEpisodeTitle, toriginDateTime, tduration, --tprogramType, --tchannelCallName,
0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0 -- program flag values default to zero, as we do not want NULL values.
FROM DW_EventClientProgramWatched pw WHERE programID IS NOT NULL AND programID != '00000000-0000-0000-0000-000000000000' -- These values should not occur, but they did in the test system AND originTime BETWEEN @StartDate AND @EndDate ELSE INSERT INTO #programWatched ( tdeviceId, tprogramId, --tprogramTitle, --tprogramEpisodeTitle, toriginDateTime, tduration, --tprogramType, --tchannelCallName,
0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0 -- program flag values default to zero, as we do not want NULL values.
FROM DW_EventClientProgramWatched pw WHERE programID IS NOT NULL AND programID != '00000000-0000-0000-0000-000000000000' -- These values should not occur, but they did in the test system
-- AccountId/SubscriberId UPDATE #programWatched SET taccountId = (SELECT accountId FROM DW_BRDB_bm_device d WHERE d.deviceId = tdeviceId)
-- program (this is the integer program ID stored in the EPG XML, not to be confused with the IPTV programId) -- a program can occur on multiple channels, so we filter channels where scheduleTime <= originTime <= scheculeTime + durationSecs UPDATE #programWatched SET tchannelCallName = (SELECT TOP 1 channelCallName FROM DW_EPG EPG WHERE tprogramId = EPG.programId AND toriginDateTime BETWEEN scheduleTime AND DATEADD(s, epg.durationSecs, epg.scheduleTime))
UPDATE #programWatched SET tprogram = (SELECT TOP 1 program FROM DW_EPG EPG WHERE tprogramId = EPG.programId AND tchannelCallName = channelCallName), tprogramTitle = (SELECT TOP 1 programTitle FROM DW_EPG EPG WHERE tprogramId = EPG.programId AND tchannelCallName = channelCallName), tprogramEpisodeTitle = (SELECT TOP 1 programEpisodeTitle FROM DW_EPG EPG WHERE tprogramId = EPG.programId AND tchannelCallName = channelCallName), tprogramType = (SELECT TOP 1 programType FROM DW_EPG EPG WHERE tprogramId = EPG.programId AND tchannelCallName = channelCallName)
-- Rating (otained from programValues, can also be obtained from programFlags) UPDATE #programWatched SET programMPAARating = (SELECT TOP 1 programValue FROM RMS_EPG..programValue pv WHERE tprogram = pv.programID AND pv.programValueTypeId = 9)
UPDATE #programWatched SET programMPAARatingVal = CASE programMPAARating WHEN 'G' THEN 10 WHEN 'PG' THEN 25 WHEN 'PG-13' THEN 30 WHEN 'R' THEN 35 WHEN 'NC-17' THEN 50 WHEN 'NRAO' THEN 60 WHEN 'NR' THEN 0 ELSE 0 END
UPDATE #programWatched SET programVChipRating = (SELECT TOP 1 programValue FROM RMS_EPG..programValue pv WHERE tprogram = pv.programID AND pv.programValueTypeId = 8)
UPDATE #programWatched SET programVChipRatingVal = CASE programVChipRating WHEN 'TV-Y' THEN 10 WHEN 'TV-Y7' THEN 20 WHEN 'TV-G' THEN 35 WHEN 'TV-PG' THEN 40 WHEN 'TV-14' THEN 45 WHEN 'TV-MA' THEN 60 ELSE 0 END
-- Genre UPDATE #programWatched SET programGenre = (SELECT TOP 1 programCategoryTypeValue FROM RMS_EPG..programCategory pc INNER JOIN RMS_EPG..programSubCategoryType psct ON psct.programSubCategoryTypeId = pc.programCategoryId INNER JOIN RMS_EPG..programCategoryType pct ON pct.programCategoryTypeId = psct.programCategoryTypeId WHERE tprogram = pc.programID)
-- Categories UPDATE #programWatched SET programCategory1 = (SELECT TOP 1 programSubCategoryTypeValue FROM RMS_EPG..programCategory pc INNER JOIN RMS_EPG..programSubCategoryType pct ON pct.programSubCategoryTypeId = pc.programCategoryId WHERE tprogram = pc.programID)
UPDATE #programWatched SET programCategory2 = (SELECT TOP 1 programSubCategoryTypeValue FROM RMS_EPG..programCategory pc INNER JOIN RMS_EPG..programSubCategoryType pct ON pct.programSubCategoryTypeId = pc.programCategoryId WHERE tprogram = pc.programID AND programSubCategoryTypeValue NOT IN (programCategory1))
UPDATE #programWatched SET programCategory3 = (SELECT TOP 1 programSubCategoryTypeValue FROM RMS_EPG..programCategory pc INNER JOIN RMS_EPG..programSubCategoryType pct ON pct.programSubCategoryTypeId = pc.programCategoryId WHERE tprogram = pc.programID AND programSubCategoryTypeValue NOT IN (programCategory1, programCategory2))
UPDATE #programWatched SET programCategory4 = (SELECT TOP 1 programSubCategoryTypeValue FROM RMS_EPG..programCategory pc INNER JOIN RMS_EPG..programSubCategoryType pct ON pct.programSubCategoryTypeId = pc.programCategoryId WHERE tprogram = pc.programID AND programSubCategoryTypeValue NOT IN (programCategory1, programCategory2, programCategory3))
-- Roles UPDATE #programWatched SET programDirectorFirstName = (SELECT TOP 1 programRoleFirstName FROM RMS_EPG..programRoleName prn INNER JOIN RMS_EPG..programRole pr ON pr.programRoleNameId = prn.programRoleNameId WHERE tprogram = pr.programID AND pr.programRoleTypeId = 2)
UPDATE #programWatched SET programDirectorLastName = (SELECT TOP 1 programRoleLastName FROM RMS_EPG..programRoleName prn INNER JOIN RMS_EPG..programRole pr ON pr.programRoleNameId = prn.programRoleNameId WHERE tprogram = pr.programID AND pr.programRoleTypeId = 2)
UPDATE #programWatched SET programWriterFirstName = (SELECT TOP 1 programRoleFirstName FROM RMS_EPG..programRoleName prn INNER JOIN RMS_EPG..programRole pr ON pr.programRoleNameId = prn.programRoleNameId WHERE tprogram = pr.programID AND pr.programRoleTypeId = 7)
UPDATE #programWatched SET programWriterLastName = (SELECT TOP 1 programRoleLastName FROM RMS_EPG..programRoleName prn INNER JOIN RMS_EPG..programRole pr ON pr.programRoleNameId = prn.programRoleNameId WHERE tprogram = pr.programID AND pr.programRoleTypeId = 7)
UPDATE #programWatched SET programProducerFirstName = (SELECT TOP 1 programRoleFirstName FROM RMS_EPG..programRoleName prn INNER JOIN RMS_EPG..programRole pr ON pr.programRoleNameId = prn.programRoleNameId WHERE tprogram = pr.programID AND pr.programRoleTypeId = 6)
UPDATE #programWatched SET programProducerLastName = (SELECT TOP 1 programRoleLastName FROM RMS_EPG..programRoleName prn INNER JOIN RMS_EPG..programRole pr ON pr.programRoleNameId = prn.programRoleNameId WHERE tprogram = pr.programID AND pr.programRoleTypeId = 6)
UPDATE #programWatched SET programActor1FirstName = (SELECT TOP 1 programRoleFirstName FROM RMS_EPG..programRoleName prn INNER JOIN RMS_EPG..programRole pr ON pr.programRoleNameId = prn.programRoleNameId WHERE tprogram = pr.programID AND pr.programRoleTypeId = 1) UPDATE #programWatched SET programActor1LastName = (SELECT TOP 1 programRoleLastName FROM RMS_EPG..programRoleName prn INNER JOIN RMS_EPG..programRole pr ON pr.programRoleNameId = prn.programRoleNameId WHERE tprogram = pr.programID AND pr.programRoleTypeId = 1)
UPDATE #programWatched SET programActor2FirstName = (SELECT TOP 1 programRoleFirstName FROM RMS_EPG..programRoleName prn INNER JOIN RMS_EPG..programRole pr ON pr.programRoleNameId = prn.programRoleNameId WHERE tprogram = pr.programID AND pr.programRoleTypeId = 1 AND programRoleFirstName NOT IN (programActor1FirstName) AND programRoleLastName NOT IN (programActor1LastName))
UPDATE #programWatched SET programActor2LastName = (SELECT TOP 1 programRoleLastName FROM RMS_EPG..programRoleName prn INNER JOIN RMS_EPG..programRole pr ON pr.programRoleNameId = prn.programRoleNameId WHERE tprogram = pr.programID AND pr.programRoleTypeId = 1 AND programRoleLastName NOT IN (programActor1LastName) AND programRoleLastName NOT IN (programActor1LastName))
UPDATE #programWatched SET programActor3FirstName = (SELECT TOP 1 programRoleFirstName FROM RMS_EPG..programRoleName prn INNER JOIN RMS_EPG..programRole pr ON pr.programRoleNameId = prn.programRoleNameId WHERE tprogram = pr.programID AND pr.programRoleTypeId = 1 AND programRoleFirstName NOT IN (programActor1FirstName) AND programRoleLastName NOT IN (programActor1LastName) AND programRoleFirstName NOT IN (programActor2FirstName) AND programRoleLastName NOT IN (programActor2LastName))
UPDATE #programWatched SET programActor3LastName = (SELECT TOP 1 programRoleLastName FROM RMS_EPG..programRoleName prn INNER JOIN RMS_EPG..programRole pr ON pr.programRoleNameId = prn.programRoleNameId WHERE tprogram = pr.programID AND pr.programRoleTypeId = 1 AND programRoleLastName NOT IN (programActor1LastName) AND programRoleLastName NOT IN (programActor1LastName) AND programRoleLastName NOT IN (programActor2LastName) AND programRoleLastName NOT IN (programActor2LastName))
UPDATE #programWatched SET programActor4FirstName = (SELECT TOP 1 programRoleFirstName FROM RMS_EPG..programRoleName prn INNER JOIN RMS_EPG..programRole pr ON pr.programRoleNameId = prn.programRoleNameId WHERE tprogram = pr.programID AND pr.programRoleTypeId = 1 AND programRoleFirstName NOT IN (programActor1FirstName) AND programRoleLastName NOT IN (programActor1LastName) AND programRoleFirstName NOT IN (programActor2FirstName) AND programRoleLastName NOT IN (programActor2LastName) AND programRoleFirstName NOT IN (programActor3FirstName) AND programRoleLastName NOT IN (programActor3LastName))
UPDATE #programWatched SET programActor4LastName = (SELECT TOP 1 programRoleLastName FROM RMS_EPG..programRoleName prn INNER JOIN RMS_EPG..programRole pr ON pr.programRoleNameId = prn.programRoleNameId WHERE tprogram = pr.programID AND pr.programRoleTypeId = 1 AND programRoleFirstName NOT IN (programActor1FirstName) AND programRoleLastName NOT IN (programActor1LastName) AND programRoleFirstName NOT IN (programActor2FirstName) AND programRoleLastName NOT IN (programActor2LastName) AND programRoleFirstName NOT IN (programActor3FirstName) AND programRoleLastName NOT IN (programActor3LastName))
UPDATE #programWatched SET programActor5FirstName = (SELECT TOP 1 programRoleFirstName FROM RMS_EPG..programRoleName prn INNER JOIN RMS_EPG..programRole pr ON pr.programRoleNameId = prn.programRoleNameId WHERE tprogram = pr.programID AND pr.programRoleTypeId = 1 AND programRoleFirstName NOT IN (programActor1FirstName) AND programRoleLastName NOT IN (programActor1LastName) AND programRoleFirstName NOT IN (programActor2FirstName) AND programRoleLastName NOT IN (programActor2LastName) AND programRoleFirstName NOT IN (programActor3FirstName) AND programRoleLastName NOT IN (programActor3LastName) AND programRoleFirstName NOT IN (programActor4FirstName) AND programRoleLastName NOT IN (programActor4LastName))
UPDATE #programWatched SET programActor5LastName = (SELECT TOP 1 programRoleLastName FROM RMS_EPG..programRoleName prn INNER JOIN RMS_EPG..programRole pr ON pr.programRoleNameId = prn.programRoleNameId WHERE tprogram = pr.programID AND pr.programRoleTypeId = 1 AND programRoleFirstName NOT IN (programActor1FirstName) AND programRoleLastName NOT IN (programActor1LastName) AND programRoleFirstName NOT IN (programActor2FirstName) AND programRoleLastName NOT IN (programActor2LastName) AND programRoleFirstName NOT IN (programActor3FirstName) AND programRoleLastName NOT IN (programActor3LastName) AND programRoleFirstName NOT IN (programActor4FirstName) AND programRoleLastName NOT IN (programActor4LastName))
UPDATE #programWatched SET programActor6FirstName = (SELECT TOP 1 programRoleFirstName FROM RMS_EPG..programRoleName prn INNER JOIN RMS_EPG..programRole pr ON pr.programRoleNameId = prn.programRoleNameId WHERE tprogram = pr.programID AND pr.programRoleTypeId = 1 AND programRoleFirstName NOT IN (programActor1FirstName) AND programRoleLastName NOT IN (programActor1LastName) AND programRoleFirstName NOT IN (programActor2FirstName) AND programRoleLastName NOT IN (programActor2LastName) AND programRoleFirstName NOT IN (programActor3FirstName) AND programRoleLastName NOT IN (programActor3LastName) AND programRoleFirstName NOT IN (programActor4FirstName) AND programRoleLastName NOT IN (programActor4LastName) AND programRoleFirstName NOT IN (programActor5FirstName) AND programRoleLastName NOT IN (programActor5LastName))
UPDATE #programWatched SET programActor6LastName = (SELECT TOP 1 programRoleLastName FROM RMS_EPG..programRoleName prn INNER JOIN RMS_EPG..programRole pr ON pr.programRoleNameId = prn.programRoleNameId WHERE tprogram = pr.programID AND pr.programRoleTypeId = 1 AND programRoleFirstName NOT IN (programActor1FirstName) AND programRoleLastName NOT IN (programActor1LastName) AND programRoleFirstName NOT IN (programActor2FirstName) AND programRoleLastName NOT IN (programActor2LastName) AND programRoleFirstName NOT IN (programActor3FirstName) AND programRoleLastName NOT IN (programActor3LastName) AND programRoleFirstName NOT IN (programActor4FirstName) AND programRoleLastName NOT IN (programActor4LastName) AND programRoleFirstName NOT IN (programActor5FirstName) AND programRoleLastName NOT IN (programActor5LastName))
UPDATE #programWatched SET programActor7FirstName = (SELECT TOP 1 programRoleFirstName FROM RMS_EPG..programRoleName prn INNER JOIN RMS_EPG..programRole pr ON pr.programRoleNameId = prn.programRoleNameId WHERE tprogram = pr.programID AND pr.programRoleTypeId = 1 AND programRoleFirstName NOT IN (programActor1FirstName) AND programRoleLastName NOT IN (programActor1LastName) AND programRoleFirstName NOT IN (programActor2FirstName) AND programRoleLastName NOT IN (programActor2LastName) AND programRoleFirstName NOT IN (programActor3FirstName) AND programRoleLastName NOT IN (programActor3LastName) AND programRoleFirstName NOT IN (programActor4FirstName) AND programRoleLastName NOT IN (programActor4LastName) AND programRoleFirstName NOT IN (programActor5FirstName) AND programRoleLastName NOT IN (programActor5LastName) AND programRoleFirstName NOT IN (programActor6FirstName) AND programRoleLastName NOT IN (programActor6LastName))
UPDATE #programWatched SET programActor7LastName = (SELECT TOP 1 programRoleLastName FROM RMS_EPG..programRoleName prn INNER JOIN RMS_EPG..programRole pr ON pr.programRoleNameId = prn.programRoleNameId WHERE tprogram = pr.programID AND pr.programRoleTypeId = 1 AND programRoleFirstName NOT IN (programActor1FirstName) AND programRoleLastName NOT IN (programActor1LastName) AND programRoleFirstName NOT IN (programActor2FirstName) AND programRoleLastName NOT IN (programActor2LastName) AND programRoleFirstName NOT IN (programActor3FirstName) AND programRoleLastName NOT IN (programActor3LastName) AND programRoleFirstName NOT IN (programActor4FirstName) AND programRoleLastName NOT IN (programActor4LastName) AND programRoleFirstName NOT IN (programActor5FirstName) AND programRoleLastName NOT IN (programActor5LastName) AND programRoleFirstName NOT IN (programActor6FirstName) AND programRoleLastName NOT IN (programActor6LastName))
UPDATE #programWatched SET programActor8FirstName = (SELECT TOP 1 programRoleFirstName FROM RMS_EPG..programRoleName prn INNER JOIN RMS_EPG..programRole pr ON pr.programRoleNameId = prn.programRoleNameId WHERE tprogram = pr.programID AND pr.programRoleTypeId = 1 AND programRoleFirstName NOT IN (programActor1FirstName) AND programRoleLastName NOT IN (programActor1LastName) AND programRoleFirstName NOT IN (programActor2FirstName) AND programRoleLastName NOT IN (programActor2LastName) AND programRoleFirstName NOT IN (programActor3FirstName) AND programRoleLastName NOT IN (programActor3LastName) AND programRoleFirstName NOT IN (programActor4FirstName) AND programRoleLastName NOT IN (programActor4LastName) AND programRoleFirstName NOT IN (programActor5FirstName) AND programRoleLastName NOT IN (programActor5LastName) AND programRoleFirstName NOT IN (programActor6FirstName) AND programRoleLastName NOT IN (programActor6LastName) AND programRoleFirstName NOT IN (programActor7FirstName) AND programRoleLastName NOT IN (programActor7LastName))
UPDATE #programWatched SET programActor8LastName = (SELECT TOP 1 programRoleLastName FROM RMS_EPG..programRoleName prn INNER JOIN RMS_EPG..programRole pr ON pr.programRoleNameId = prn.programRoleNameId WHERE tprogram = pr.programID AND pr.programRoleTypeId = 1 AND programRoleFirstName NOT IN (programActor1FirstName) AND programRoleLastName NOT IN (programActor1LastName) AND programRoleFirstName NOT IN (programActor2FirstName) AND programRoleLastName NOT IN (programActor2LastName) AND programRoleFirstName NOT IN (programActor3FirstName) AND programRoleLastName NOT IN (programActor3LastName) AND programRoleFirstName NOT IN (programActor4FirstName) AND programRoleLastName NOT IN (programActor4LastName) AND programRoleFirstName NOT IN (programActor5FirstName) AND programRoleLastName NOT IN (programActor5LastName) AND programRoleFirstName NOT IN (programActor6FirstName) AND programRoleLastName NOT IN (programActor6LastName) AND programRoleFirstName NOT IN (programActor7FirstName) AND programRoleLastName NOT IN (programActor7LastName))
-- Channel (provider) Call Letters, Display Name and Type -- Is this correct? Should we get the channelId from the schedule table? -- Is this efficient? View execution plan
UPDATE #programWatched SET tchannelId = (SELECT TOP 1 c.channelId FROM RMS_EPG..channel c INNER JOIN RMS_EPG..schedule s on s.channelID = c.channelID WHERE s.programId = tprogram)
UPDATE #programWatched SET callLetters = (SELECT TOP 1 c.channelCallLetters FROM RMS_EPG..channel c INNER JOIN RMS_EPG..schedule s on s.channelID = c.channelID WHERE s.programId = tprogram and s.channelId = tchannelId)
UPDATE #programWatched SET displayName = (SELECT TOP 1 c.channelDisplayName FROM RMS_EPG..channel c JOIN RMS_EPG..schedule s on s.channelID = c.channelID WHERE s.programId = tprogram and s.channelId = tchannelId)
UPDATE #programWatched SET type = (SELECT TOP 1 c.channelType FROM RMS_EPG..channel c INNER JOIN RMS_EPG..schedule s on s.channelID = c.channelID WHERE s.programId = tprogram and s.channelId = tchannelId)
UPDATE #programWatched SET networkAffiliation = (SELECT TOP 1 c.channelNetworkAffiliation FROM RMS_EPG..channel c INNER JOIN RMS_EPG..schedule s on s.channelID = c.channelID WHERE s.programId = tprogram and s.channelId = tchannelId)
IF @TerseMode = 0 SELECT * FROM #programWatched ORDER BY toriginDateTime ELSE -- Get only Genre, title, show date/time, rating, call letters SELECT tDeviceId, tprogramTitle, tprogramEpisodeTitle, programGenre, toriginDateTime, programMPAARating, programVCHIPRating, tchannelCallName FROM #programWatched ORDER BY toriginDateTime
I also tried a query that populates some of its columns via subqueries. The query works fine when executed by the SQL Sevrer Query Analyzer, meaning that all columns contain values, but when executed from SSRS, the columns that are poulated by the subqueries are empty, and only the columns that are not set by subqueries contain values:
SELECT PW.DeviceID, PW.originTime AS 'When Watched', PW.programID, PW.Duration AS 'Duration Seconds',
(SELECT TOP 1 programTitle FROM DW_EPG WHERE DW_EPG.programId = PW.programId AND PW.originTime BETWEEN DW_EPG.scheduleTime AND DATEADD(second, durationSecs, DW_EPG.scheduleTime)) AS Title,
(SELECT TOP 1 program FROM DW_EPG WHERE DW_EPG.programId = PW.programId AND PW.originTime BETWEEN DW_EPG.scheduleTime AND DATEADD(second, durationSecs, DW_EPG.scheduleTime)) As program,
(SELECT TOP 1 programCategoryTypeValue FROM RMS_EPG..programCategory PC INNER JOIN RMS_EPG..programSubCategoryType PSCT ON psct.programSubCategoryTypeId = PC.programCategoryId INNER JOIN RMS_EPG..programCategoryType PCT ON PCT.programCategoryTypeId = PSCT.programCategoryTypeId WHERE PC.programID = (SELECT TOP 1 program FROM DW_EPG WHERE DW_EPG.programId = PW.programId AND PW.originTime BETWEEN DW_EPG.scheduleTime AND DATEADD(second, durationSecs, DW_EPG.scheduleTime))) AS Genre,
(SELECT TOP 1 programSubCategoryTypeValue FROM RMS_EPG..programCategory PC INNER JOIN RMS_EPG..programSubCategoryType PSCT ON psct.programSubCategoryTypeId = PC.programCategoryId INNER JOIN RMS_EPG..programCategoryType PCT ON PCT.programCategoryTypeId = PSCT.programCategoryTypeId WHERE PC.programID = (SELECT TOP 1 program FROM DW_EPG WHERE DW_EPG.programId = PW.programId AND PW.originTime BETWEEN DW_EPG.scheduleTime AND DATEADD(second, durationSecs, DW_EPG.scheduleTime))) AS Category
FROM DW_EventClientProgramWatched PW ORDER BY DeviceId, programId, originTime
I have a backup application that uses SQL as the backend (both 2000 and 2005) The backup application performs a specific function that enters data into the Database but does not have any reporting. I am looking for a way to query the DB directly to see if there is any info I can grab. But the problem is I don't know where its stored. So my questions) are:
Is there anyway to tell what tables get updated by a certain process. For example if I run this one action how could I then tell what tables were effected or even what data was changed. I tried looking for a logging function that would list this but did not find it. I also tried looking for some type of real time monitor. I even tried looking for a way to search for records / tables that had been recently updated.
I am new to SQL so not sure I am using the correct terms but any help would be appreciated. Also this is SQL2000 and a test server
I'm working on a project that is being built piece by piece. The first partis in place. I will occasionally need to either change thing within a table(only adding fields) or add stored procedures etc.What is the best way of making these changes to the production databasewithout interfering with the existing data? The users are remote and I won'tnecessarily have direct access to the server. I'll have to walk the primaryuser through the process.TIA--Jake
I'm new to this whole SQL Server 2005 thing as well as database design and I've read up on various ways I can integrate business constraints into my database. I'm not sure which way applies to me, but I could use a helping hand in the right direction.
A quick explanation of the various tables I'm dealing with: WBS - the Work Breakdown Structure, for example: A - Widget 1, AA - Widget 1 Subsystem 1, and etc. Impacts - the Risk or Opportunity impacts for the weights of a part/assembly. (See Assemblies have Impacts below) Allocations - the review of the product in question, say Widget 1, in terms of various weight totals, including all parts. Example - September allocation, Initial Demo allocation, etc. Mostly used for weight history and trending Parts - There are hundreds of Parts which will eventually lead to thousands. Each part has a WBS element. [Seems redundant, but parts are managed in-house, and WBS elements are cross-company and issued by the Government] Parts have Allocations - For weight history and trending (see Allocations). Example, Nut 17 can have a September 1st allocation, a September 5th allocation, etc. Assemblies - Parts are assemblies by themselves and can belong to multiple assemblies. Now, there can be multiple parts on a product, say, an unmanned ground vehicle (UGV), and so those parts can belong to a higher "assembly" [For example, there can be 3 Nut 17's (lower assembly) on Widget 1 Subsystem 2 (higher assembly) and 4 more on Widget 1 Subsystem 5, etc.]. What I'm concerned about is ensuring that the weight roll-ups are accurate for all of the assemblies. Assemblies have Impacts - There is a risk and opportunity impact setup modeled into this design to allow for a risk or opportunity to be marked on a per-assembly level. That's all this table represents.
A part is allocated a weight and then assigned to an assembly. The Assemblies table holds this hierarchical information - the lower assembly and the higher one, both of which are Parts entries in the [Parts have Allocations] table.
Therefore, to ensure proper weight roll ups in the [Parts have Allocations] table on a per part-basis, I would like to check for any inserts, updates, deletes on both the [Parts have Allocations] table as well as the [Assemblies] table and then re-calculate the weight roll up for every assembly. Now, I'm not sure if this is a huge performance hog, but I do need to keep all the information as up-to-date and as accurate as possible. As such, I'm not sure which method is even correct, although it seems an AFTER DML trigger is in order (from what I've gathered thus far). Keep in mind, this trigger needs to go through and check every WBS or Part and then go through and check all of it's associated assemblies and then ensure the weights are correct by re-summing the weights listed.
If you need the design or create script (table layout), please let me know.
Are there any limitations or gotchas to updating the same table whichfired a trigger from within the trigger?Some example code below. Hmmm.... This example seems to be workingfine so it must be something with my specific schema/code. We'reworking on running a SQL trace but if anybody has any input, fireaway.Thanks!create table x(Id int,Account varchar(25),Info int)GOinsert into x values ( 1, 'Smith', 15);insert into x values ( 2, 'SmithX', 25);/* Update trigger tu_x for table x */create trigger tu_xon xfor updateasbegindeclare @TriggerRowCount intset @TriggerRowCount = @@ROWCOUNTif ( @TriggerRowCount = 0 )returnif ( @TriggerRowCount > 1 )beginraiserror( 'tu_x: @@ROWCOUNT[%d] Trigger does not handle @@ROWCOUNT[color=blue]> 1 !', 17, 127, @TriggerRowCount) with seterror, nowait[/color]returnendupdate xsetAccount = left( i.Account, 24) + 'X',Info = i.Infofrom deleted, inserted iwhere x.Account = left( deleted.Account, 24) + 'X'endupdate x set Account = 'Blair', Info = 999 where Account = 'Smith'
This Audit Trigger is Generic (i.e. non-"Table Specific") attach it to any tabel and it should work. Be sure and create the 'Audit' table first though.
The following code write audit entries to a Table called 'Audit' with columns 'ActionType' //varchar 'TableName' //varchar 'PK' //varchar 'FieldName' //varchar 'OldValue' //varchar 'NewValue' //varchar 'ChangeDateTime' //datetime 'ChangeBy' //varchar
using System; using System.Data; using System.Data.SqlClient; using Microsoft.SqlServer.Server;
public partial class Triggers { //A Generic Trigger for Insert, Update and Delete Actions on any Table [Microsoft.SqlServer.Server.SqlTrigger(Name = "AuditTrigger", Event = "FOR INSERT, UPDATE, DELETE")]
public static void AuditTrigger() { SqlTriggerContext tcontext = SqlContext.TriggerContext; //Trigger Context string TName; //Where we store the Altered Table's Name string User; //Where we will store the Database Username DataRow iRow; //DataRow to hold the inserted values DataRow dRow; //DataRow to how the deleted/overwritten values DataRow aRow; //Audit DataRow to build our Audit entry with string PKString; //Will temporarily store the Primary Key Column Names and Values here using (SqlConnection conn = new SqlConnection("context connection=true"))//Our Connection { conn.Open();//Open the Connection //Build the AuditAdapter and Mathcing Table SqlDataAdapter AuditAdapter = new SqlDataAdapter("SELECT * FROM Audit WHERE 1=0", conn); DataTable AuditTable = new DataTable(); AuditAdapter.FillSchema(AuditTable, SchemaType.Source); SqlCommandBuilder AuditCommandBuilder = new SqlCommandBuilder(AuditAdapter);//Populates the Insert command for us //Get the inserted values SqlDataAdapter Loader = new SqlDataAdapter("SELECT * from INSERTED", conn); DataTable inserted = new DataTable(); Loader.Fill(inserted); //Get the deleted and/or overwritten values Loader.SelectCommand.CommandText = "SELECT * from DELETED"; DataTable deleted = new DataTable(); Loader.Fill(deleted); //Retrieve the Name of the Table that currently has a lock from the executing command(i.e. the one that caused this trigger to fire) SqlCommand cmd = new SqlCommand("SELECT object_name(resource_associated_entity_id) FROM ys.dm_tran_locks WHERE request_session_id = @@spid and resource_type = 'OBJECT'", conn); TName = cmd.ExecuteScalar().ToString(); //Retrieve the UserName of the current Database User SqlCommand curUserCommand = new SqlCommand("SELECT system_user", conn); User = curUserCommand.ExecuteScalar().ToString(); //Adapted the following command from a T-SQL audit trigger by Nigel Rivett //http://www.nigelrivett.net/AuditTrailTrigger.html SqlDataAdapter PKTableAdapter = new SqlDataAdapter(@"SELECT c.COLUMN_NAME from INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk , INFORMATION_SCHEMA.KEY_COLUMN_USAGE c where pk.TABLE_NAME = '" + TName + @"' and CONSTRAINT_TYPE = 'PRIMARY KEY' and c.TABLE_NAME = pk.TABLE_NAME and c.CONSTRAINT_NAME = pk.CONSTRAINT_NAME", conn); DataTable PKTable = new DataTable(); PKTableAdapter.Fill(PKTable);
switch (tcontext.TriggerAction)//Switch on the Action occuring on the Table { case TriggerAction.Update: iRow = inserted.Rows[0];//Get the inserted values in row form dRow = deleted.Rows[0];//Get the overwritten values in row form PKString = PKStringBuilder(PKTable, iRow);//the the Primary Keys and There values as a string foreach (DataColumn column in inserted.Columns)//Walk through all possible Table Columns { if (!iRow[column.Ordinal].Equals(dRow[column.Ordinal]))//If value changed { //Build an Audit Entry aRow = AuditTable.NewRow(); aRow["ActionType"] = "U";//U for Update aRow["TableName"] = TName; aRow["PK"] = PKString; aRow["FieldName"] = column.ColumnName; aRow["OldValue"] = dRow[column.Ordinal].ToString(); aRow["NewValue"] = iRow[column.Ordinal].ToString(); aRow["ChangeDateTime"] = DateTime.Now.ToString(); aRow["ChangedBy"] = User; AuditTable.Rows.InsertAt(aRow, 0);//Insert the entry } } break; case TriggerAction.Insert: iRow = inserted.Rows[0]; PKString = PKStringBuilder(PKTable, iRow); foreach (DataColumn column in inserted.Columns) { //Build an Audit Entry aRow = AuditTable.NewRow(); aRow["ActionType"] = "I";//I for Insert aRow["TableName"] = TName; aRow["PK"] = PKString; aRow["FieldName"] = column.ColumnName; aRow["OldValue"] = null; aRow["NewValue"] = iRow[column.Ordinal].ToString(); aRow["ChangeDateTime"] = DateTime.Now.ToString(); aRow["ChangedBy"] = User; AuditTable.Rows.InsertAt(aRow, 0);//Insert the Entry } break; case TriggerAction.Delete: dRow = deleted.Rows[0]; PKString = PKStringBuilder(PKTable, dRow); foreach (DataColumn column in inserted.Columns) { //Build and Audit Entry aRow = AuditTable.NewRow(); aRow["ActionType"] = "D";//D for Delete aRow["TableName"] = TName; aRow["PK"] = PKString; aRow["FieldName"] = column.ColumnName; aRow["OldValue"] = dRow[column.Ordinal].ToString(); aRow["NewValue"] = null; aRow["ChangeDateTime"] = DateTime.Now.ToString(); aRow["ChangedBy"] = User; AuditTable.Rows.InsertAt(aRow, 0);//Insert the Entry } break; default: //Do Nothing break; } AuditAdapter.Update(AuditTable);//Write all Audit Entries back to AuditTable conn.Close(); //Close the Connection } }
//Helper function that takes a Table of the Primary Key Column Names and the modified rows Values //and builds a string of the form "<PKColumn1Name=Value1>,PKColumn2Name=Value2>,......" public static string PKStringBuilder(DataTable primaryKeysTable, DataRow valuesDataRow) { string temp = String.Empty; foreach (DataRow kColumn in primaryKeysTable.Rows)//for all Primary Keys of the Table that is being changed { temp = String.Concat(temp, String.Concat("<", kColumn[0].ToString(), "=", valuesDataRow[kColumn[0].ToString)].ToString(), ">,")); } return temp; } }
The trick was getting the Table Name and the Primary Key Columns. I hope this code is found useful.
I need to update information for a user and if the user is classified as a primary (@blnPrimary) then I need to update information for all users within his agency (AgencyUniqueId). The issue is that the second UPDATE to "cdds_User_Profile" always returns a rowcount of 0 (should be 1) even though the values for "@Original_AgencyUniqueId" and "@Original_UserId" are correct. This is just a snippet of the whole procedure. I'm trying to implement similar logic in other parts of the procedure and I'm observing the same behavior there as well. Any help anyone can provide is greatly appreciated. </p><pre>/*** Update User Profile ***/UPDATE [cdds_User_Profile] SET [FirstName] = @FirstName, [LastName] = @LastName, [Title] = @Title, [Phone] = @Phone, [AcctType] = @AcctType, [AcctStatus] = @AcctStatus, [LastUpdatedDate] = GETDATE() WHERE ([FirstName] = @Original_FirstName AND [LastName] = @Original_LastName AND [Title]=@Original_Title AND [Phone]=@Original_Phone AND [AcctType]=@Original_AcctType AND [AcctStatus]= @Original_AcctStatus AND [AgencyUniqueId] = @Original_AgencyUniqueIdAND [UserId] = @Original_UserId);IF @@ROWCOUNT = 0BEGINSET @err_message = 'Data has been edited by another user since you began viewing this information.'RAISERROR (@err_message,11, 1)ROLLBACK TRANSACTIONRETURNEND IF @@ERROR <> 0 BEGINROLLBACK TRANSACTIONRETURN ENDIF @blnPrimary = 1 BEGIN IF LOWER(@AcctStatus) <> LOWER(@AgencyAcctStatus)/*** Update Users Acct. Status ***//* update all users in same agency profile */UPDATE [cdds_User_Profile] SET [AcctStatus] = @AcctStatus,[LastUpdatedDate] = GETDATE() WHERE ([AgencyUniqueId] = @Original_AgencyUniqueIdAND [UserId] = @Original_UserId); IF @@ROWCOUNT = 0BEGINSET @err_message = 'Data for this agency has been edited by another user since you began viewing this information.'RAISERROR (@err_message,11, 1)ROLLBACK TRANSACTIONRETURNENDIF @@ERROR <> 0 BEGINROLLBACK TRANSACTIONRETURN ENDEND</pre><pre>
I am trying to do selective updates for rows where a column matches a column in another table. I want to do something like this, only 'this' does not work, and nothing else I could think of (I tried joins also) worked. What am I missing? I hope this explanation makes sense.
UPDATE queryresultsmodel SET queryresultsmodel.tableforcedoutdate = getdate() Where Exists (Select tablename from queryresultsmodel q inner join orphanul o on q.tablename = o.name)
Has anyone had any problems on one row updates on a table where you have defined horizontal and vertical partitioning of the data to be replicated? When I execute an update clause that modifies just one row the log reader misses the modification and it does not get replicated to the other databases.
If I do the same update clause but on several rows then all the modifications are read by the log reader and the replication task goes ok.
