Firing A Trigger When A User Updates Data But Not When A Stored Procedure Updates Data
Dec 19, 2007
I have a project that consists of a SQL db with an Access front end as the user interface. Here is the structure of the table on which this question is based:
Code Block
create table #IncomeAndExpenseData (
recordID nvarchar(5)NOT NULL,
itemID int NOT NULL,
itemvalue decimal(18, 2) NULL,
monthitemvalue decimal(18, 2) NULL
)
The itemvalue field is where the user enters his/her numbers via Access. There is an IncomeAndExpenseCodes table as well which holds item information, including the itemID and entry unit of measure. Some itemIDs have an entry unit of measure of $/mo, while others are entered in terms of $/yr, others in %/yr.
For itemvalues of itemIDs with entry units of measure that are not $/mo a stored procedure performs calculations which converts them into numbers that has a unit of measure of $/mo and updates IncomeAndExpenseData putting these numbers in the monthitemvalue field. This stored procedure is written to only calculate values for monthitemvalue fields which are null in order to avoid recalculating every single row in the table.
If the user edits the itemvalue field there is a trigger on IncomeAndExpenseData which sets the monthitemvalue to null so the stored procedure recalculates the monthitemvalue for the changed rows. However, it appears this trigger is also setting monthitemvalue to null after the stored procedure updates the IncomeAndExpenseData table with the recalculated monthitemvalues, thus wiping out the answers.
How do I write a trigger that sets the monthitemvalue to null only when the user edits the itemvalue field, not when the stored procedure puts the recalculated monthitemvalue into the IncomeAndExpenseData table?
I need a script that inserts the data of an excel sheet into a table. If something already exists it should leave it, unless it's edited in the excel sheet and so on and so on. This proces has to go through a stored procedure... ...But how?
Hello, I am working on a project that involves one part where a field's value needs to be changed when the user updates the record. Here is the situation in detail: There is an InputData table where the user enters new records or changes existing records. There is a field called "calculated" in this table which has a default value of 'no'. A stored procedure runs math calculations on all the InputData records where the calculated field = 'no'. At the end of this stored procedure, it sets the calculated field = 'yes'. When new records are added by the user their "calculated" field value is 'no' by default so that the next time the stored procedure is executed, it only runs the math calculations on the new records. The problem is, if a user changes an existing record, the "calculated" field needs to be changed from 'yes' to 'no' so that the stored procedure recalculates the math for the modified record. How do I change the value from 'yes' to 'no' on records that the user modifies? Thanks.
I have the following stored procedure: CREATE PROCEDURE user1122500.sp_modifyOrganization(@Name nvarchar(100),@Location nvarchar(50),@Url nvarchar (250),@Org_Type nvarchar (50),@Par_Org_Id uniqueidentifier,@Row_Id uniqueidentifier,@Error_Code int OUTPUT,@Error_Text nvarchar(768) OUTPUT)ASDECLARE @errorMsg nvarchar(512)DECLARE @spName sysname SELECT @spName = Object_Name(@@ProcID)SET @Error_Code = 0 IF @Url > ' ' BEGIN UPDATE USER1122500.ORGANIZATION SET URL = @Url ,UPDATED = GETDATE() WHERE ROW_ID = @Row_Id IF @@error <> 0 BEGIN EXEC user1122500.sp_tagValueList @errorMsg OUTPUT, N'ROW_ID', @Row_Id, N'URL', @Url SET @Error_Code = 51002 -- Error Message as created in the ERROR_LIST table SELECT @Error_Text = (SELECT DESC_TEXT FROM USER1122500.ERROR_LIST WHERE ERROR_CODE = @Error_Code) RAISERROR(@Error_Text, 11, 1, @spName, @@error, 'ORGANIZATION', @errorMsg) RETURN(@@error) END END IF @Org_Type > ' ' BEGIN UPDATE USER1122500.ORGANIZATION SET ORG_TYPE = @Org_Type ,UPDATED = GETDATE() WHERE ROW_ID = @Row_Id IF @@error <> 0 BEGIN EXEC user1122500.sp_tagValueList @errorMsg OUTPUT, N'ROW_ID', @Row_Id, N'ORG_TYPE', @Org_Type SET @Error_Code = 51002 -- Error Message as created in the ERROR_LIST table SELECT @Error_Text = (SELECT DESC_TEXT FROM USER1122500.ERROR_LIST WHERE ERROR_CODE = @Error_Code) RAISERROR(@Error_Text, 11, 1, @spName, @@error, 'ORGANIZATION', @errorMsg) RETURN(@@error) END END IF @Par_Org_Id IS NOT NULL BEGIN UPDATE USER1122500.ORGANIZATION SET PAR_ORG_ID = @Par_Org_Id ,UPDATED = GETDATE() WHERE ROW_ID = @Row_Id IF @@error <> 0 BEGIN EXEC user1122500.sp_tagValueList @errorMsg OUTPUT, N'ROW_ID', @Row_Id, N'PAR_ORG_ID', @Par_Org_Id SET @Error_Code = 51002 -- Error Message as created in the ERROR_LIST table SELECT @Error_Text = (SELECT DESC_TEXT FROM USER1122500.ERROR_LIST WHERE ERROR_CODE = @Error_Code) RAISERROR(@Error_Text, 11, 1, @spName, @@error, 'ORGANIZATION', @errorMsg) RETURN(@@error) END END IF @Name > ' ' OR @Location > ' ' BEGIN IF EXISTS (SELECT ROW_ID FROM USER1122500.ORGANIZATION WHERE NAME = @Name AND LOCATION = @Location) BEGIN EXEC user1122500.sp_tagValueList @errorMsg OUTPUT, N'NAME', @Name, N'LOCATION', @Location SET @Error_Code = 55004 -- Error Message as created in the ERROR_LIST table SELECT @Error_Text = (SELECT DESC_TEXT FROM USER1122500.ERROR_LIST WHERE ERROR_CODE = @Error_Code)-- RAISERROR(@Error_Text, 10, 1, @spName, @Error_Code, 'ORGANIZATION', @errorMsg) SELECT @Error_Text = (SELECT REPLACE(@Error_Text,'sp_name',@spName)) SELECT @Error_Text = (SELECT REPLACE(@Error_Text,'err_cd',@Error_Code)) SELECT @Error_Text = (SELECT REPLACE(@Error_Text,'tbl_name','ORGANIZATION')) SELECT @Error_Text = (SELECT REPLACE(@Error_Text,'err_msg',@errorMsg)) RETURN(@Error_Code) END IF @Name > ' ' BEGIN UPDATE USER1122500.ORGANIZATION SET NAME = @Name ,UPDATED = GETDATE() WHERE ROW_ID = @Row_Id IF @@error <> 0 BEGIN EXEC user1122500.sp_tagValueList @errorMsg OUTPUT, N'ROW_ID', @Row_Id, N'PAR_ORG_ID', @Name SET @Error_Code = 51002 -- Error Message as created in the ERROR_LIST table SELECT @Error_Text = (SELECT DESC_TEXT FROM USER1122500.ERROR_LIST WHERE ERROR_CODE = @Error_Code) RAISERROR(@Error_Text, 11, 1, @spName, @@error, 'ORGANIZATION', @errorMsg) RETURN(@@error) END END IF @Location > ' ' BEGIN UPDATE USER1122500.ORGANIZATION SET LOCATION = @Location ,UPDATED = GETDATE() WHERE ROW_ID = @Row_Id IF @@error <> 0 BEGIN EXEC user1122500.sp_tagValueList @errorMsg OUTPUT, N'ROW_ID', @Row_Id, N'LOCATION', @Location SET @Error_Code = 51002 -- Error Message as created in the ERROR_LIST table SELECT @Error_Text = (SELECT DESC_TEXT FROM USER1122500.ERROR_LIST WHERE ERROR_CODE = @Error_Code) RAISERROR(@Error_Text, 11, 1, @spName, @@error, 'ORGANIZATION', @errorMsg) RETURN(@@error) END END ENDGO
This is the code that runs it: string strSP = "sp_modifyOrganization"; SqlParameter[] Params = new SqlParameter;
string strParOrgID = null; if (this.ddlParentOrg.SelectedItem.Value != "") { strParOrgID = this.ddlParentOrg.SelectedItem.Value; } Params[0] = new SqlParameter("@Name", txtName.Text); Params[1] = new SqlParameter("@Location", this.txtLocation.Text); Params[2] = new SqlParameter("@Url", this.txtURL.Text); Params[3] = new SqlParameter("@Org_Type", this.txtOrgType.Text); //Params[4] = new SqlParameter("@Par_Org_Id", strParOrgID); Params[4] = new SqlParameter("@Par_Org_Id", "CA1FBC83-D978-48F1-BCBC-E53AD5E8A321".ToUpper());
Params[5] = new SqlParameter("@Row_Id", "688f2d10-1550-44f8-a62c-17610d1e979a".ToUpper()); // Params[5] = new SqlParameter("@Row_Id", lblOrg_ID.Text); Params = new SqlParameter("@Error_Code", -1); Params[7] = new SqlParameter("@Error_Text", ""); Params[4].SqlDbType = SqlDbType.UniqueIdentifier; Params[5].SqlDbType = SqlDbType.UniqueIdentifier; Params.Direction = ParameterDirection.Output; Params[7].Direction = ParameterDirection.Output;
try { this.dtsData = SqlHelper.ExecuteDataset(ConfigurationSettings.AppSettings["SIM_DSN"], CommandType.StoredProcedure, strSP, Params); if (Params.Value.ToString() != "0") { lblError.Text = "There was an error: " + Params.Value.ToString()+ "###" + Params[7].Value.ToString(); lblError.Visible = true; } } //catch (System.Data.SqlClient.SqlException ex) catch (System.InvalidCastException inv) { lblError.Text = lblOrg_ID.Text + "<br><br>" + inv.ToString() + inv.Message + inv.StackTrace + inv.HelpLink; lblError.Visible = true; } catch (Exception ex) { lblError.Text = lblOrg_ID.Text + "<br><br>" + ex.ToString(); lblError.Visible = true; // return false; } This is the exception being generated: System.InvalidCastException: Invalid cast from System.String to System.Guid. at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream) at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior) at System.Data.SqlClient.SqlCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior) at System.Data.Common.DbDataAdapter.FillFromCommand(Object data, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet) at Microsoft.ApplicationBlocks.Data.SqlHelper.ExecuteDataset(SqlConnection connection, CommandType commandType, String commandText, SqlParameter[] commandParameters) in C:Program Files\_vsNETAddOnsMicrosoft Application Blocks for .NETData Access v2CodeVBMicrosoft.ApplicationBlocks.DataSQLHelper.vb:line 542 at Microsoft.ApplicationBlocks.Data.SqlHelper.ExecuteDataset(String connectionString, CommandType commandType, String commandText, SqlParameter[] commandParameters) in C:Program Files\_vsNETAddOnsMicrosoft Application Blocks for .NETData Access v2CodeVBMicrosoft.ApplicationBlocks.DataSQLHelper.vb:line 458 at development.youthleadercert.com.share.ascx.organizationForm.btnAdd_Click(Object sender, EventArgs e) in c:documents and settingsmark rubinvswebcachedevelopment.youthleadercert.comshareascxorganizationform.ascx.cs:line 352
I have no idea what field is even causing the error, nor do I see that I'm even using a GUID field. I've been stuck on this for 2 days. Any help?
Hi dear users, I have created procedure which main function is to update row of the table. Below, there is SQL code of this procedure.
Code Block
CREATE PROCEDURE Zakl_UpdateRow @zakl_id INT, @zakl_nazwa VARCHAR(25), @zakl_miasto VARCHAR(20), @zakl_ulica VARCHAR(30) AS UPDATE Zaklady SET Zakl_Nazwa=@zakl_nazwa, Zakl_Miasto=@zakl_miasto, Zakl_Ulica=@zakl_ulica WHERE Zakl_ID=@zakl_id
When I execute this procedure I have to give all declared variables: "@zakl_id", "@zakl_nazwa" and so on. Sometimes I don't want to update all columns in the row but only one f.ex name of the street ("Zakl_Ulica"). And I want other colums of the row to stay the same ("Zakl_Nazwa","Zakl_Miasto"). There is no point in rewriting values of "Zakl_Nazwa","Zakl_Miasto" as I want only to change value of "Zakl_Ulica".
this is execution clause
Code Block
EXECUTE Zakl_UpdateRow @zakl_id=70, @zakl_ulica='kosciuszki 7' If I don't ascribe value to the variables (@zakl_nazwa, @zakl_miasto ), the columns in the row ("Zakl_Nazwa","Zakl_Miasto") should stay unchanged.
So what should I modify in my procedure, to have it working?
DESCRIPTION: I have an FTP server set up to log via ODBC into a table FTPLog. The trigger on table FTPLog fires when new files are received to process and load the file via a stored procedure.
CREATE TRIGGER tr_new_file ON FTPLog AFTER INSERT AS SET NOCOUNT ON DECLARE @filename varchar(50), @logtime datetime DECLARE c1 CURSOR FOR SELECT filename, logtime FROM inserted OPEN c1 FETCH NEXT FROM c1 INTO @filename, @logtime WHILE @@fetch_status = 0 BEGIN EXEC sp1 @filename, @logtime FETCH NEXT FROM c_inserted INTO @filename, @logtime END CLOSE c1 DEALLOCATE c1 END
PROBLEM: There are multiple problems with this setup. The first problem is that when the stored procedure gets executed it takes a long time to process the file and the FTP server never returned a completion code to the ftp client and ended with a connection time out from the client. My users keep asking if the FTP failed but it didnt fail. The server returned a completion code too late.
PROBLEM2: When multiple files are ftp to the server on the same session, only the first one gets process. Even though my code loops through all the records because the processing takes a long time the second one never gets executed. If I replace the EXEC sp1 statement with a PRINT statement then it's working fine.
We have on demand snapshot replication set up between 2 servers. When the subscriber applies the snapshot, our stored procedures start executing very slowly. Updating statistics and rebuilding indexes does not resolve the problem, however; executing sp_recompile on the affected stored procedures does fix the problem. Is this a known issue with replication? Is there a better workaround than manually recompiling stored procedures after every snapshot?
I have 14 databases, the last database - 14th one will have lookup tables only. The other 13 databases will have these lookup tables and data tables. At the end of each day I will make updates for lookup tables on 14th database, I want to be able to push the updates to any or some of the 13 databases. Look up tables will have only upto 100 rows, so I am not concerned about the bandwidth. What is the best way to accomplish this.
I don't know if the title for the subject is appropriate here, anywayhere goes:This process was set up by someone and I have inherited it. I have asql2000 database that has about 13 tables that get populated with datafrom 3 different databases. I have identified where each of this datacomes from, and the stored procedures that do the updates, inserts, anddeletes, and the jobs that run these stored procedure to do theupdates, except for one table. The updates for all the other tablesare done through scheduled jobs. For the one table I know where thedata comes from and the stored procedure that needs to run to do theupdate on the table, however I have not been able to identify theprocess that runs the stored procedure.I am hoping that someone can give me a clue as to how to find out wherea stored procedure is being used - or any other hint as to how I couldgo about finding out how this table gets updated.ThanksKR
I have been successful with DTS packages and various SQL statements. However, I have a new challenge. I have a table in an SQL Server database. One of the columns is employee number and a column for department number(which is not populated) In a remote AS400 file I have the employees number and department number. I want to create a package to connect to remote table and update SQL Server table with department number where the two tables match on the employee number.
I'm using Visual Basic 2005 Express and SQL Server 2005 Express. I have textboxes on a VB form linked to 2 database tables.
I am wondering if it is possible to use just ONE BindingNavigator to manage data entry and updates to THE database tables. I initially thought I could manage the tables but have I encountered some problems:
i)When I entered a new record, and clicked on the SAVE BUTTON the new record the textboxes for the 1st table saves the record to the database, but the textboxes for 2nd table still retained data in them and are not saving theirs to the database.
ii) The same textboxes for the 2nd table are NOT allowing for updates too! Or, could it simply be that it is not possible to use this method for data entry and updates?
I need to make a job that will update up to 8000 rows with the list description of 'berkhold' to 'berknew' in SQL 2000. This is something that I have to do with several projects manually every day by doing the following 2 steps.
SELECT ListDescription, CRRecordID FROM dbo_BerkleyGroupInventory WHERE ListDescription ="BerkHold" AND CRCallDateTime<'1/1/2003' AND CRCallResultCode ='CC' ORDER BY CRRecordID
I then scroll to the 8000th row and copy the CrrecordID and run the following query
UPDATE dbo.berkleygroupinventory SET listdescription ='berknew' WHERE ListDescription ='BerkHold' AND CRRecordID <=5968432 AND CRCallDateTime ='1/1/2003' AND CRCallResultCode='CC'
I'm sure there's an easier way to do this, but I'm very new to SQL and haven't figured it out yet
I have 14 databases, the last database - 14th one will have lookup tables only. The other 13 databases will have these lookup tables and data tables. At the end of each day I will make updates for lookup tables on 14th database, I want to be able to push the updates to any or some of the 13 databases. Look up tables will have only upto 100 rows, so I am not concerned about the bandwidth. What is the best way to accomplish this.
I am running a Access97 front end with a SQL Server 7 backend. On records with an ntext datatype, you are only allowed to update records if the ntext field is null. The tables are linked from access. You get a "cannot update linked table" and "ODBC error #306." Any suggestions??
We have a legacy database whose data needs to be included in our yet-to-be-built sql 2005 data warehouse. Some of the tables don€™t have any natural candidates for a primary key. (Of course, we also need to add other data to the mix.)
Suppose we load the empty warehouse initially. In following loads we don€™t want to include those records that haven€™t changed from the first load (€œduplicates€?) but we also don€™t want to delete the contents of the entire warehouse because of the load time. Any ideas/best practices how to handle €œincremental updates€? to a warehouse would be appreciated.
I got anywhere from a couple hundred to a hundred thousand records that need to be updated or inserted into their SQL Server 2005 end destination. What are some of the best ways to accomplish this? Right now we are doing it manually through line by line updates and inserts. Would I use BC or some other bulk import tool?
I have a table that is increasing quite largely each day. By now, I have average 300 million of records over 2.5 years. Before we received our new interface, the data we received was aggregated and thus not that big.The problem is that the table is so huge that I cannot use the Slowly Changing Component. I was thinking about making a temp table where I load the incremental data before I load it into the final data mart table.Based on this temporary table I use a script to compare the temp data with the already existing data in the data mart. However, this requires a compare of each records (300 mil records).
I posted the questions in sql forum and got good sql statement to work with it.. However, I want to see if there is a way to do it in SSIS..
May be this is really basic questions but I am having hard time to do it in sql server 2005 SSIS..
I have a flat file that I want to merge with table in SQL server 2005.
1> I have successfully created a data flow task to import data from flat file to Table X (new table I created for this package).
Now here is my question. I have a Table A already in the database with the same column structure as of TableX (Both the tables have 20 columns/same Name/Same design).
I want to merge Table A and Table X and stored the data in TableA. However, I just don't want to merge blindly, I need to insert a new row in Table A only if the same row does not exist in Table A (there is no primary key, i am looking certain fields to see if the rows are same)..
Here is an example: Table A -------------- 1 test test1 test2 test3 test4 test5 2 test test6 test7 test8 test9 test10
Table X ------------ 1 test test1 test2 test99 test4 test5 2 test test98 test97 test 96 test95 test94 -------------------------------------------------------- Now, I want to only insert row 2 of Table X since there is match on 4 of the fields in row1.. The new Table A should look like
NEW Table A' -----------------
test test1 test2 test3 test4 test5 test test6 test7 test8 test9 test10 test test98 test97 test 96 test95 test94
------------------------------------ I think, I could do this using Execute SQL task and write all the code in sql, but that will be cumbersome and time consuming.. Is there a simpler way to achieve this?
I have been given a request by a business analyst to update the text 'old' to 'new' within the column names / measure names and associated calculations within a PowerPivot model. There are hundred of columns / measures / calculations, etc. which need to be renamed.
Is there any way of updating these changes to the model other than making these changes manually or is there some way of doing the following type of operation in PowerPivot; -
UPDATE tblColumnNames SET Column_name, etc REPLACE ('old','new', all columns),('old','new', all measures),('old','new', all calculations) FROM tblColumnNames
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 have a table customer wich has the columns phone_number(char type) and ok_to_call(bit type). There are already data in the table and the column ok_to_call only contains the value false for every row.
Now i want to update the latter column. I have a text file with a list of phone numbers and i want that all the rows in the Customer table(phone_number column)that matches the number in the text file to update ok_to_call to true.
This is to be done in SSIS(Integration Services). I'm new at this and i've looked around that tool but is a lot of items, packages and stuff so i dont know where to begin.
Would appreciate help on how to solve this issue in SSIS. What controlflow/Data flows to use,wich items and packages to use, how to configure and how to link together?
I will be getting data in either Excel or Access form on a daily basis. I would like to automate the process of converting this (excel or access) data to a table in an existing SQL database. Since this conversion needs to performed on a daily basis, note that I need to update the table that contains data from the day before.
Is it possible to do this and if it is possible, can someone tell me how to do it.
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.
Ok, I think this may have a simple answer. Basically I have no problems in setting up QueryString/Control/etc parameters when I use SELECT in the Configure Data Source Wizard as it prompts me for the necessary parameters. But when I try to use the Configure Data Source Wizard with an UPDATE, INSERT or DELETE it does NOT prompt me for the required parameters.Is this a bug or am I just missing something? Do I have to put them in manually or something?Thanks!
I recently inherited multiple databases for a research study. These databases use an Access front end with the tables stored on SQL Server. Currently, there is a folder for each database on a network drive. I make changes to the front ends (forms, reports etc.) in a development version of the dbs, test them, have a user test them, and then import them to the production front end.
Each user has a copy of the front end on their 'C:' drive. The previous developer put together a separate VB app that copies the changed mdb files from the network drive to their 'C:' drive. This doesn't seem like the best solution to me but I haven't come up with a better one. I would appreciate any input.
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?
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.
We have a Table that contains a schedule. The schedule has certain pieces of information that are required to be updated by certain members of the organization, specifically Shift, Start Date, and Line. Each record also has a column for the customer that it is for.
The schedule has a Column, [CSR] which lists the name of the person who is responsible for updating it. Format of that is domainnamejohndoe
What we would like to do is block domainnamejohndoe from being able to update or insert on any of the records that he is not responsible for.
So if he attempts to change the shift for example, it denies the change and possible pops up an error indicating that the change was blocked because he is not the responsible party.
Input in this is through a query which views the table data in MS Access. It cannot be moved to a Form without some serious redesign, so I cannot put the block in there. I assumed that this could be blocked with a Trigger that checks the [CSR] column, matches it to the logged in user through the suser_sname(suser_sid()) and then either allows or denies the update.
So is this a viable idea, or should I explore the move to an input form and make the change there in MS Access via VBA.
At first we started with just an audit trail, but I started to think that the audit trail would be needed only for records that should be allowed to be changed and that we should deny changes to those who are not allowed to make them.
Below is the audit trail trigger, I figure that the change should occur somewhere within that first IF statement, or make the first IF the second, and the first should be the permission check.
At this point I am stuck as to how to block the updates. I tried using Deny but that was not working. Filtering the results by only showing the logged in user their records is not an option as they need visibility to the entire schedule.
This is for SQL 2000 SP4/Windows 2000 SP4
CREATE TRIGGER audit_mschange ON dbo.T_PP_Table_2 FOR update AS IF (UPDATE([Start Dt]) OR Update(shift) or Update (comments) or Update(status) or Update (line)) BEGIN
SELECT ins.[PP Ord No], del.line,ins.line, del.shift,ins.shift, del.comments,ins.comments, del.status, ins.status, getdate(), suser_sname(suser_sid()), del.[Start Dt], ins.[Start Dt] FROM inserted ins, deleted del WHERE ins.[PP Ord No]=del.[PP Ord No] AND ins.autonumber=del.autonumber AND (ins.status <> del.status OR ins.[Start Dt] <> del.[Start Dt] or ins.status <> del.status or ins.comments <> del.comments or ins.line <> del.line ) END
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...
Hi I am Importing data in datatable using SSIS package . I made trigger on that table on insert. The trigger on insert is not firing on that table Please help Thanks CP
