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?
View 4 Replies
ADVERTISEMENT
May 27, 2014
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?
View 6 Replies
View Related
Sep 10, 2007
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.
View 9 Replies
View Related
Apr 27, 2005
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?
View 2 Replies
View Related
Jan 5, 2008
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?
Best Regards
Maciej
View 3 Replies
View Related
Jun 11, 2008
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.
SOLUTIONS and SUGGESTIONS highly appreciated.
View 13 Replies
View Related
Mar 21, 2007
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?
View 3 Replies
View Related
Oct 26, 2007
Guys,
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.
Any suggestions and inputs would help
Thanks
View 1 Replies
View Related
Mar 7, 2006
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
View 2 Replies
View Related
Oct 13, 2005
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.
View 3 Replies
View Related
Jul 22, 2007
Hi Guys,
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?
Thanks you.
View 1 Replies
View Related
Aug 6, 2007
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
View 11 Replies
View Related
Oct 26, 2007
Guys,
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.
Any suggestions and inputs would help
Thanks
View 5 Replies
View Related
Sep 19, 2000
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??
View 1 Replies
View Related
May 12, 2006
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.
TIA,
Bill
View 7 Replies
View Related
Apr 28, 2008
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?
View 5 Replies
View Related
Jun 22, 2015
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).
View 3 Replies
View Related
Sep 10, 2007
Hi all,,
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?
Thanks in advance.
View 6 Replies
View Related
Sep 1, 2015
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
View 2 Replies
View Related
Jan 12, 2004
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?
bye
View 10 Replies
View Related
Sep 18, 2007
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?
Thanks.
View 3 Replies
View Related
Aug 14, 2006
Hi,
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?
Regards
/Tomas
View 3 Replies
View Related
Feb 16, 2008
Hello,
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.
Thanks in advance,
Joe green
View 1 Replies
View Related
Mar 2, 2006
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.
Thank you,
Igor
View 2 Replies
View Related
Jun 8, 2007
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!
View 5 Replies
View Related
Jun 18, 2004
Hi all,
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.
Thanks,
Monk
View 1 Replies
View Related
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.
View 7 Replies
View Related
May 5, 2008
Hi,
Does anyone know if there's a way to log batch updates done using SQL queries without using a trigger or a cursor?
Thanks in advance,
Vinod
View 6 Replies
View Related
Dec 28, 2007
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?
Following is the table structure:
Customer_Master(custmastercode, customer_company_name,updated_by)
Following is the trigger:
ALTER TRIGGER [TR_UPDATE_CUST]
ON [dbo].[CUSTOMER_MASTER]
AFTER UPDATE
AS
BEGIN
SET NOCOUNT ON;
IF EXISTS (SELECT * FROM inserted)
BEGIN
declare @custcode int
Declare @message varchar(5000)
Declare @custommessage varchar(2000)
Declare @CUSTMASTERCODE int
Declare @CUSTOMER_COMPANY_NAME varchar(50)
Set @message = 'Changes in customer account number ' + Cast ((@custcode) as varchar(10)) + ': '
select @custcode = [CUSTMASTERCODE],@UPDATED_BY = [UPDATED_BY] from inserted
Set @message = 'Changes in customer account number ' + Cast ((@custcode) as varchar(10)) + ': '
IF(update([CUSTOMER_COMPANY_NAME]))
Begin
select @UCUSTOMER_COMPANY_NAME = [CUSTOMER_COMPANY_NAME] from deleted
select @CUSTOMER_COMPANY_NAME = [CUSTOMER_COMPANY_NAME] from inserted
Set @custommessage = 'Customer company name changed from ' + @UCUSTOMER_COMPANY_NAME + ' to ' + @CUSTOMER_COMPANY_NAME + '.'
Set @message = @message + @custommessage
End
Set @message = @message + ' Updated by ' + @UPDATED_BY + ' at ' + CAST(getdate() AS VARCHAR(20))+ '.'
INSERT INTO [CHANGE_HISTORY]
([CUSTMASTERCODE]
,[CHANGE_DETAILS])
VALUES (@custcode, @message)
END
END
View 7 Replies
View Related
Mar 9, 2015
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.
View 9 Replies
View Related
Nov 27, 2007
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
INSERT INTO T_MS_RB_AUDIT
(ord_no,line_tank_old,line_tank_new,shift_old,shif t_new,comments_old,comments_new,status_old,status_ new,trx_dt,trx_username,
start_dt_old,start_dt_new)
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
View 11 Replies
View Related
Oct 30, 2007
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...
Regards
Karen
View 5 Replies
View Related
Oct 6, 2007
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
View 1 Replies
View Related
