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

View 2 Replies


ADVERTISEMENT

Keep Track Of When And By Who A Record Was Created And Also When And By Who The Record Was Last Updated

May 26, 2008

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

View 10 Replies View Related

SQL Help: Get The Record I Just Updated

Jul 6, 2007

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
 

View 12 Replies View Related

Checking For A New Record

Jan 5, 2005

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.

View 3 Replies View Related

How To Retrieve Last Updated Record

Dec 11, 2006

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

View 2 Replies View Related

How Can I Be Notified When Record Is Updated

Jun 9, 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) ..

View 1 Replies View Related

Identity Of An Updated Record

Nov 15, 2001

Does anyone know how to get the indentity of the last updated record in a table. I need it for a cascading trigger.

Thank you,

Jody

View 3 Replies View Related

How To Get The Record I Just Updated By Mistake

Mar 10, 2008

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?

Thanks a lot.

View 1 Replies View Related

Checking If No Record Is Returned

Aug 1, 2004

hi,

I basically want the .net version of rs.eof
does anyone know what the code would be using the execute scalar method?

Thanks

View 2 Replies View Related

Retrieve Last Inserted Or Updated Record

Nov 22, 2007

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

View 4 Replies View Related

Auto Update Value When Record Gets Updated

Feb 21, 2006

I have a table that contains a field LastUpdated. This field should contains the date of the last update of the record.

Is there a way to auto-update this field value to the getdate() value each time the record is "touched" by an UPDATE sql command?

Thanks in advance,
Joannes

View 1 Replies View Related

Display Last Updated Record By A User

Mar 30, 2008

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


Thank you appreciate it.

View 2 Replies View Related

(sql 2005) How To Get The Record I Just Updated By Mistake

Mar 10, 2008

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?

Thanks a lot.

View 1 Replies View Related

Checking To See If A Record Exists Before Inserting

Feb 14, 2007

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)

comm.Parameters.Add("@BuilderID", System.Data.SqlDbType.Int)
comm.Parameters("@BuilderID").Value = LBBuilderID.Text

comm.Parameters.Add("@OptionNO", System.Data.SqlDbType.NVarChar)
comm.Parameters("@OptionNO").Value = DDLProdID.SelectedItem.Value

comm.Parameters.Add("@OptionName", System.Data.SqlDbType.NVarChar)
comm.Parameters("@OptionName").Value = DDLProdname.SelectedItem.Value

LBerror.Text = DDLProdname.SelectedItem.Value & " was added to your Option List."

Try
'open connection
conn.Open()
'execute
comm.ExecuteNonQuery()
Catch
'Display error message
LBerror.Text = "There was an error adding this Option. Please try again."
Finally
'close connection
conn.Close()
End Try

End If

End Sub  
 

View 8 Replies View Related

Problem In Checking Null Record

Apr 30, 2007

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. 

View 1 Replies View Related

Checking A Table To See If A Record Already Exists

Sep 19, 2007

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.

Anybody got any ideas?

View 4 Replies View Related

Checking Whether Record ID Exists In Another Query

Jan 10, 2014

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

Order by pg.grid_id

View 4 Replies View Related

Checking To See If A Record Exists Else Insert

Apr 30, 2008

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.

View 6 Replies View Related

T-SQL (SS2K8) :: How To Get Last Inserted / Updated Record In Server

Apr 24, 2015

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?

View 1 Replies View Related

How To Determine Which Record Was Updated Using A Update Trigger?

May 8, 2008



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.


SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

-- =============================================

-- Author: <Author,,Name>

-- Create date: <Create Date,,>

-- Description: <Description,,>

-- =============================================

CREATE TRIGGER TTSUpdate

ON SiteInfo

FOR UPDATE

AS

declare @SiteID varchar(10)

declare @Body2 varchar(2000)

declare @Sitename varchar(50)

declare @TTSCreate varchar(30)

declare @TTSCreator varchar(50)

declare @Subject2 varchar (100)



SELECT @SiteID = SiteID,@Sitename = AccountName,@TTSCreator = TTSOwner,@TTSCreate = TTSCreatedDate

from SiteInfo



SET @Body2 = 'New TTS site created: ' + @Sitename + ' With TTS Site ID:' + @SiteID + ' TTS was created on: ' + @TTSCreate + ' By:' + @TTSCreator

SET @subject2 = 'New TTS site created: ' + @Sitename

EXEC msdb.dbo.sp_send_dbmail

@profile_name = 'TTSAdmin',

@recipients = 'email address here',

@subject = @subject2,

@body = @body2

GO

View 3 Replies View Related

Checking To See If A Record Exists And If So Update Else Insert

Feb 9, 2007

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.

http://blogs.conchango.com/jamiethomson/archive/2006/09/12/SSIS_3A00_-Checking-if-a-row-exists-and-if-it-does_2C00_-has-it-changed.aspx

Thanks Jamie!

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/

Phil

View 60 Replies View Related

Checking Record Consistency Between Tables Using SSIS

Feb 1, 2008

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.

Any ideas?

Thank you for your help!

cdun2

View 3 Replies View Related

Record Not Updated Or Deleted When Any Column Consists The Null Value

Oct 23, 2007

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 

View 4 Replies View Related

Does The UPDATE Trigger Fire When A Record Is Updated Or Only When It Is Deleted?

Jun 15, 2004

I've gotten conflicting info about this in the past so I thought I'd try to get clarification.

When a record is deleted, I'm sure it fires the delete trigger. Does it also fire the update trigger?

Thanks

View 3 Replies View Related

T-SQL (SS2K8) :: Return Primary Key From (updated) Record Of Merge Statement

Apr 16, 2014

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.

CREATE PROCEDURE [dbo].[usp_ComputerInformation_UPSERT](
@FullComputerName varChar(50) = NULL
,@ComputerDescription varChar(255) = NULL
,@ComputerSystemType varChar(128) = NULL
,@ComputerManufacturer varChar(128) = NULL

[code]....

View 4 Replies View Related

SQL Server 2008 :: Run Store Procedure When Table Is Updated With Record

Jul 15, 2015

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%'

View 2 Replies View Related

How Can I Set A Update Trigger On A Table In Database A After A Record Had Been Updated From Another Database B?

Jan 22, 2008



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.

Best Regards,
Hans

View 8 Replies View Related

Save Updated Date When Row Is Updated

Apr 6, 2008

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. 

View 3 Replies View Related

TOUGH INSERT: Copy Sale Record/Line Items For Duplicate Record

Jul 20, 2005

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

View 6 Replies View Related

How To Create An Copy Of A Certain Record Except One Specific Column That Must Be Different &&amp; Insert The New Record In The Table

Sep 1, 2006

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

Thanks.

View 6 Replies View Related

Ways To Make This Work: Several Selectable Related Record For One Main Record.

Apr 6, 2007

Hey all!



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.



Thanks in advance,

Johan

View 5 Replies View Related

Query Timeouts When Updating A Record Retrieved Through A Websphere JDBC Datasource - Possible Record Locking Problem

Apr 7, 2008

Hi,

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.

Code snippet - getting the record thru JDBC:


DataSource wsDataSource = serviceLocator.getDataSource("jdbc/dsSQLServer");
Connection wsCon = wsDataSource.getConnection();


// wsCon.setAutoCommit(false); //have tried with and without this flag - same results

Statements stmt = wsCon.createStatement();


String sql = "SELECT * FROM Person where personID = 12345";
ResultSet rs = stmt.executeQuery(sql);


if(rs.next()){
System.out.println(rs.getString("lastName"));
}

if (rs != null){
rs.close();
}
if (stmt != null) {

stmt.close();
}
if (wsCon != null) {

wsCon.close();
}

View 1 Replies View Related

SSIS: Multi-Record File Extract With 9 Record Types

Feb 26, 2008

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.


View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved