Need Help Checking To See If More Than 1 Record Updated.
May 21, 2006
I am trying to write a trigger that, on an update, will check to see if more than one record is being updated. If more than one record is being updated, rollback the transaction and print a message. If not, then commit the transaction and print an OK message.
My problem is I am having troubles writing this trigger. I will post my SQL trigger code so far.
I am new with Triggers, so any help or advice is appreciated.
Code:
CREATE TRIGGER trgPaperCheck
ON BOOK
AFTER UPDATE
AS
IF UPDATE(Paperback)
BEGIN
DECLARE @count AS INT
SELECT @count = COUNT(Paperback)
FROM Book
IF @count > 1
BEGIN
PRINT('Only one Book record can be updated at a time!')
ROLLBACK TRANSACTION
END
ELSE IF @count =1
BEGIN
PRINT('Paperback Updated')
COMMIT TRANSACTION
END
END
I have not yet succeeded in getting an aswer to this in a previous post, so I'll try again and rephrase the question
I have 2 fields, 1 called 'Created' the other 'Updated'
I would like to create a function, user procedure ,whatever in SQL something along the lines of...
FUNCTION myUID() RETURN Date(Today)+Time(Now)+USERNAME END FUNCTION
so that the value returned by the above pseudo code is something like '20080526T21:01:05.620SamL' where the date part is in yyyy0m0d format
I have got this working fine for the default 'Created' field but the calculated (and persisted) field comes up with something about being non-deterministic and refuses to play
Clearly something along these lines is a very straightforward requirement (I have it working fine in my 20+ year old database) , but have spent a week trying to get an answer (even from this forum) without success, so would be grateful for any help that you can provide me with to get round this barrier
Hi - apologies if this is not the right forum for this - I've searched a bit and this seems to be the best fit. I have the following problem: I want to update records in a table that fit certain criteria. The way the insert logic works make sure that there will always be only one record that fits the criteria and I'd like to get the ID value of that record once the update went through. So here is what I tried: 1 UPDATE Timeslot 2 SET StartTime = @StartTime, EndTime = @EndTime 3 WHERE (ProfessionalID = @ProfessionalID) AND (ProviderLocationID = @ProviderLocationID) AND (RequestID IS NULL) AND (StartTime > @StartTime) AND 4 (EndTime < @EndTime); 5 SELECT SCOPE_IDENTITY()
My hope was that the select scope_identity would return the Timeslot ID of the row that was affected, but it doesn't. How do I get that row? Thanks!!! Oliver
Good morning everyone I am writing a windows forms application that will work similar to the windows messenger popup. I need this application to display a message to the user whenever a record is added to table in my DB. Any help would be appreciated in explaining how I can check the DB for new records.
I have some set of records in my table. The same set of records will be updated often. Now I have a column as "lastupdated" While i am displaying the records in a datagrid, The LAST UPDATED record should only be displayed. Means, the recently updated records should be displayed in datagrid. Pls give me the sql code / i am also in need of a Stored procedure for this. I am working in SQL 2005
I want to build an windows application by using a visual C# to Notify the user that his data in the database had been changed ..such like "New Message In Your Mail Box Alert"..So I need to know if there is way that to let the SQL Server send a notify (just like Trigger) ..
Hi, I was opening a web page, that has a grid view representing the records of a table, I updated a record of the sql table by mistake, but I am not sure which record, is there any way in Sql server to get the last record just updated few minutes ago. I hyave admin permissions on the DB.
Also, is there a way to know the value that was just there before the changes?
Hi I have an application which get any change from database using sql dependency. When a record is inserted or updated it will fire an event and my application get that event and perform required operation. On the event handler I am usin select ID,Name from my [table]; this will return all record from database. I just want to get the record which is inserted or updated. Can u help me in that. Take care Bye
I would like to show when leads updated last their records in database. An automated report that tells me when the last date was that the leads updated an entry, only 1 entry per lead.
select Lead,LastUpdated from dbo.KPITbl
I have a table with data that looks like this:
Lead LastUpdated ----------- ----------------------- JOHN SMITH 2008-03-26 08:45:00 JOHN SMITH 2008-03-20 09:33:00 MEG RYAN 2008-02-21 16:16:00 JOHN SMITH 2008-02-21 16:19:00 MEG RYAN 2008-02-21 16:22:00 JOHN SMITH 2008-03-28 16:10:00 JOHN SMITH 2008-03-28 08:49:00 JOHN SMITH 2008-03-23 19:23:00 MARK MCRAE 2008-03-27 03:12:00 MARK MCRAE 2008-03-26 08:48:00 MARK MCRAE 2008-03-26 08:46:00 JOHN SMITH 2008-03-26 08:47:00 JOHN SMITH 2008-03-26 08:48:00 ALLAN WHITE 2008-03-26 08:43:00 ALLAN WHITE 2008-03-26 08:40:00 JOHN SMITH 2008-03-26 08:48:00
Hi, I was opening a web page, that has a grid view representing the records of a table, I updated a record of the sql table by mistake, but I am not sure which record, is there any way in Sql server to get the last record just updated few minutes ago. I hyave admin permissions on the DB.
Also, is there a way to know the value that was just there before the changes?
I can't seem to get this work. I'm using SQL2005 I want to check if a record exists before entering it. I just can't figure out how to check it before hand. Thanks in advance. Protected Sub BTNCreateProdIDandName_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles BTNCreateProdIDandName.Click ' Define data objects Dim conn As SqlConnection Dim comm As SqlCommand
' Reads the connection string from Web.config Dim connectionString As String = ConfigurationManager.ConnectionStrings("HbAdminMaintenance").ConnectionString ' Initialize connection conn = New SqlConnection(connectionString)
' Check to see if the record exists If comm = New SqlCommand("EXISTS (SELECT (BuilderID, OptionNO FROM optionlist WHERE (BuilderID = @BuilderID) AND (OptionNO = @OptionNO)", conn)
Then 'if the record is exists display this message. LBerror.Text = "This item already exists in your Option List." Else
'If the record does not exist, add it. FYI - This part works fine by itself. comm = New SqlCommand("INSERT INTO [OptionList] ([BuilderID], [OptionNO], [OptionName]) VALUES (@BuilderID, @OptionNO, @OptionName)", conn)
Hello, I am using sql server 2000 as my backend.If there is any null record in my table , I am getting following error:*********** Conversion from type 'DBNull' to type 'String' is not valid. ************** to overcome this error I want to check if its null then don't do anything else do something.I have tried like this:If rs.fields("ph").value="NULL" thenelse.................end if But this does not solve my problem.Please help me i anyone can.Thanks.
I've got two tables, one containing a list of company names(approx 10,000 records), the other containing a list of company employees (approx 30,000 records) joined by the CompanyID column.
I have a third table (approx 700 records) containing new employees to be added to the employee table. Each record in this table has the employees details plus the name of their company.
I want to write a query that will check each row in the third table to see if a) the employee exists in the Employees table b) the company exists in the Companies table and c) the employee is listed under the correct company
The query should also handle any combination of the above. So if the company doesn't exist but the employee does, create the company on the companies table and update the appropriate record on the employees table with the new CompanyID etc. etc.
Oh, forgot to mention. The company names in the third table won't be exactly the same as the ones in the Company table so will need to use CharIndex.
I'm trying to check which price grids are in use using the price grid_id, and seeing whether this grid_id exists in another query that checks all active contracts. If the grid_id is present (active) I want to return 'Yes', if not I want it to return 'No'.
There are 385 price grids, but my query is only returning the 315 that are active, and ignoring any that are not used. My code is below, how I can see all the records whether Yes or No:
Select distinct pg.grid_id [Price Grid], pg.grid_name [Grid Name], case when exists (Select c.grid_id from customers c inner join deltickhdr dh on dh.acct = c.custnum and dh.stage <5 where c.type = 'C' and dh.dticket is not null) then 'Yes' else 'No' end [Active]
From gridhdr pg inner join customers c on c.grid_id = pg.grid_id
I am checking to see if the source record is available in the target table using a lookup transformation and if not found i have to insert this record. I have connected the error flow of the lookup transformation to the target. I am acheiving expected results, but is this the best practise? I have not connected to the green arrow to any task.
We are getting data feed from Oracle database in our project. Everyday we will need to track if any rows got inserted/updated/deleted in the source and get that update right into our data warehouse.
Currently we are taking a dump of the required table (as it is) to our staging DB and comparing it with previous day data to track the changes (column by column comparison). This approach is working currently but has performance bottleneck. There is no tracking column (eg. last modified date or time) in source that will give us any idea of what got changed. Also there is no identity key or primary key in the source data.
Is there a way in SQL Server to get that inserted/updated records only instead of comparing column by column to track the changes?
Im using a trigger to check updates on particular table and execute a email. it works but it doesnt show the right record im looking into one table called SiteInfo. here is my code Im using sql 2005, can someone look at my code or the select statement.
I've decided to post this as a sticky given the frequency this question is asked.
For those of you wishing to build a package that determines if a source row exists in the destination and if so update it else insert it, this link is for you.
If you want to do a similar concept to Jamie's blog post above, but with the Konesan's Checksum Transformation to quickly compare MANY fields, you can visit here: http://www.ssistalk.com/2007/03/09/ssis-using-a-checksum-to-determine-if-a-row-has-changed/
Hello, I have an SSIS package that affects data in fourteen tables. This SSIS packge is supposed to insert, update, delete and apply various business rules to the data in the new database tabes in the same way that it occurs in the DTS package that affects the old tables in the former database.
After both the DTS packge and SSIS packages are run, the data in the fourteen tables from both databases need to mirror each other. I figure this is the best way that I can know that the new SSIS package is working correctly.
Is there something I can do in an SSIS package that can compare all of the tables between the two databases, record for record then (maybe as error output) produce output in the case where a mistmatch was detected between two given tables? Would a lookup transformation be helpful for this kind or thing, or should I think about doing it some other way?
The old database is on SQL Server 2000, and the new one on SQL Server 2005 64 bit. I've run into problems creating a linked server between the two to where I could just run queries to do this kind of check.
Hi All In My application when i want to work with sqldatasource for updation and delteion tasks it is working properly when all the columns consists the data. If any of the column consists the null values its not updating and deleting. Advices are needed.Thank uBaba
I'm using a Merge statement to update/insert values into a table. The Source is not a table, but the parameters from a Powershell script. I am not using the Primary Key to match on, but rather the Computer Name (FullComputerName).
I am looking on how-to return the Primary Key (ComputerPKID) of an updated record as "chained" scripts will require a Primary Key, new or used.As an aside: the code below does return the newly generated Primary Key of an Inserted record.
I wanted to know if it is possible to run a stored proc (report) when a new record is inserted / updated into a certain table. Also if it could do so for records with a column that meet a certain criteria ie. sell description like '%test%'
Hi guys, may I know is it possible to create an update trigger like this ? Assuming there are two database, database A and database B and both are having same tables called 'Payments' table. I would like to update the Payments records on database A automatically after Payments records on database B had been updated. I can't use replication because both tables might having different records and some records are the same. Hope can get any assistance here, thank you.
Hi,I want to save the last modification date when the row is updated. I have a column called "LastModification" in the table, every time the row is update I want to set the value of this column to the current date. So far all I know is that I need to use a trigger and the GetDate() function, but could any body help me with how to set the value of the column to getdate()? thanks for your help.
I have a client who needs to copy an existing sale. The problem isthe Sale is made up of three tables: Sale, SaleEquipment, SaleParts.Each sale can have multiple pieces of equipment with correspondingparts, or parts without equipment. My problem in copying is when I goto copy the parts, how do I get the NEW sale equipment ids updatedcorrectly on their corresponding parts?I can provide more information if necessary.Thank you!!Maria
Hi I have a table with a user column and other columns. User column id the primary key.
I want to create a copy of the record where the user="user1" and insert that copy in the same table in a new created record. But I want the new record to have a value of "user2" in the user column instead of "user1" since it's a primary key
Sorry for the less then descriptive post title but I didn't find a better way to describe it. I'm developing an app in the express editions of VB and SQLserver. The application is a task/resource scheduler. The main form will have a datepicker or weekly overview and show all tasks scheduled per day. The problem is, I've got one or more people assigned to tasks and I wonder what's the best way to design this. Personally, I'd go for one Task table, a People table and a table that provides a link between them (several record per task, one for each person assigned linking TaskID and PplID). However, I don't see a nice way of showing this data to the end user, allowing him to edit/add etc on ONE screen.
To fix that the only way I see is just add columns to the Task table for every person with select boxes. This way everything can be done on one simple screen. This obviously does present some future issues.
On top of this, which people are available on a day varies and there should be an option to allow a user to set who is available on a specific day. Which would lead me to my first idea and add another table that would provide this. but then I'm having design issues again for the form.
I'm kinda stuck atm, can anyone shed some light on this. I'm sure there is an elegant way of doing this but I'm failing at finding it.
We're running a Sage CRM install with a SQL Server 2000 database at the back end. We're using the Sage web services API for updating data and a JDBC connection to retrieve data as it's so much quicker.
If I retrieve a record using the JDBC connection and then try and update the same record through the web services, the query times out as if the record is locked for updates. Has anyone experienced anything similar or know what I'm doing wrong? If I just use DriverManager.getConnection() to establish the connection instead of the datasource, and then continue with the same code I don't get these record locking problems. Please find more details below.
Thanks, Sarah
The JDBC provider for the datasource is a WebSphere embedded ConnectJDBC for SQL Server DataSource, using an implementation type of 'connection pool datasource'. We are using a container managed J2C authentication alias for logging on.
This is running on a Websphere Application Server v6.1.
I am attempting to create a multi-record file (as described in my last thread) and have found the following set of instructions very helpful: http://vsteamsystemcentral.com/cs21/blogs/steve_fibich/archive/2007/09/25/multi-record-formated-flat-file-with-ssis.aspx
I have been able to create a sample file with two of my record types.
I now need to build on this further, because I have 9 record types in total that need to be extracted to a single flat file.
does anyone have any ideas how I might extend the example above to include more record types or know of another means of achieving this?
Thanks in advance for any help you might be able to provide.