|
hi fellows
i'm trying to create triggers in Sql Server 2000 and my query doesn't work.
can anyone plz tell me the query syntax to create trigger for the following operations:
1. Insert
2. Update
3. Delete
awaitng ur replies
Saira
|
|
|
|
|
Saira Tanwir wrote: can anyone plz tell me the query syntax to create trigger
The syntax is here[^]
|
|
|
|
|
Saira Tanwir wrote: 1. Insert
CREATE TRIGGER blah_trigger ON blah1 FOR INSERT AS INSERT INTO blah2 ...
|
|
|
|
|
I have a database that a user can search for using the begins with, exact match, or contains option. I did a request.querystring to pull in that information from the previous page where the user selects one of those options and types is what he is searching for. The problem is that I cannot figure out how to display the users result when they select to search by exact match, begins with, or contains. The following is the code that I used. Can anyone tell me what is wrong with it and give me some leads on how to get it fixed.
<title>Table Display
<%Dim FieldHead, SearchMethod, TextBox, Table, DB, RS, strSql%>
<%'Request the information from the previous page'%>
<%FieldHead = Request.QueryString("fieldhead")
SearchMethod = Request.QueryString("SrchMthd")
TextBox = Request.QueryString("textbox")
Table = Request.QueryString("Table")
%>
<%'search method if the user selects begins with%>
<%If SearchMethod = "begin" Then%>
<%Set DB = Server.CreateObject("ADODB.Connection")
DB.Open ("PROVIDER=Microsoft.Jet.OLEDB.4.0;DATA SOURCE= C:\Inetpub\wwwroot\ASP\ThomasTate\North.mdb")
Set RS = Server.CreateObject("ADODB.Recordset")
strSql = "SELECT * FROM " & Table " WHERE " & FieldHead & " LIKE '" & TextBox & "%'"
RS.Open (strSql), DB
End IF
%>
<%'search method if the user selects contains%>
<%If SearchMethod = "contain" Then%>
<%Set DB = Server.CreateObject("ADODB.Connection")
DB.Open ("PROVIDER=Microsoft.Jet.OLEDB.4.0;DATA SOURCE= C:\Inetpub\wwwroot\ASP\ThomasTate\North.mdb")
Set RS = Server.CreateObject("ADODB.Recordset")
strSql = "SELECT * FROM " & Table " WHERE " & FieldHead & " LIKE '%" & TextBox & "%'"
RS.Open (strSql), DB
End IF%>
<%'search method if the user selects exact match%>
<%If SearchMethod = "exact" Then%>
<%Set DB = Server.CreateObject("ADODB.Connection")
DB.Open ("PROVIDER=Microsoft.Jet.OLEDB.4.0;DATA SOURCE= C:\Inetpub\wwwroot\ASP\ThomasTate\North.mdb")
Set RS = Server.CreateObject("ADODB.Recordset")
strSql = "SELECT * FROM " & Table " WHERE " & FieldHead & = TextBox
RS.Open (strSql), DB
End IF%>
Ty
|
|
|
|
|
Your code should use:
... " WHERE " & FieldHead & " LIKE '" & Replace(TextBox, "'", "''") & "%' Instead of:
... " WHERE " & FieldHead & " LIKE '" & TextBox & "%'" Otherwise it will break if the user enters a single-quote into his search value.
The normal way to show the results is to iterate through each row and column in the result set. However, you might find the following faster:
'Open the recordset and render the data in HTML format.
For Each objField In objRS.Fields
Call Response.Write("<td nowrap><b>" & _
Server.HtmlEncode(objField.Name) & "</b></td>" & vbCRLF)
Next
Call Response.Write("</tr>" & vbCRLF)
Call Response.Write("<tr>")
If Not objRs.eof Then
strData = objRS.GetString( , , "#<#/td#>##<#td#>#", _
"#<#/td#>##<#/tr#>##<#tr#>##<#td nowrap#>#", "#&#nbsp;")
strData = Replace(Replace(Replace(strData, "#&#", Chr(1)), "&", "&"), Chr(1), "&")
strData = Replace(Replace(Replace(strData, "#<#", Chr(1)), "<", "<"), Chr(1), "<")
strData = Replace(Replace(Replace(strData, "#>#", Chr(1)), ">", ">"), Chr(1), ">")
strData = "<td>" & Replace(Left(strData, Len(strData) - 15), "</tr>", "</tr>" & vbCRLF)
Call Response.Write(strData)
Else
Call Response.Write("<td colspan=""" & objRs.Fields.Count & """>No records found.</td></tr>" & vbCRLF)
End If
Call Response.Write("</table>" & vbCRLF)The funny replace stuff is a quick way to get it to render each field as a separate HTML table cell.
Andy
|
|
|
|
|
hi there,
I've got a table like this:
CREATE TABLE [dbo].[one_Messages] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[RecipientID] [int] NOT NULL ,
[SenderID] [int] NOT NULL ,
[DateTimeSent] [datetime] NOT NULL ,
[IsRead] [bit] NOT NULL ,
[Body] [varchar] (1024) COLLATE Latin1_General_CI_AS NOT NULL
) ON [PRIMARY]
Now please consider that I want to allow only 20 Messages per conversation (SenderID/RecipientID combination). In my stored procedure, I'd like to INSERT a new conversation-record first and then DELETE the oldest messages which exceed the number of 20. It's quite hard for me to explain this in english, do you get the point? After every INSERT, I'd like make sure to keep a maximum number of 20 messages and DELETE the rest. How can this be done with a DELETE statement?
Any help is greatly appreceated. Thanks in advance.
/matthias
I love deadlines. I like the whooshing sound they make as they fly by.
[Douglas Adams]
|
|
|
|
|
Do the insert then do the delete based on the DateTimeSend, or you could do it on the ID (because it will be ever increasing)
e.g.
DELETE one_Messages
WHERE RecipientID = @RecipientID
AND SenderID = @SenderID
AND ID NOT IN (SELECT TOP 20 ID FROM one_Messages
WHERE RecipientID = @RecipientID
AND SenderID = SenderID
ORDER BY ID DESC)
The inner select gets the most recent 20 items.
Does this help?
|
|
|
|
|
Perfect. Thanks a bunch!
/matthias
I love deadlines. I like the whooshing sound they make as they fly by.
[Douglas Adams]
|
|
|
|
|
Place the delete code in a trigger.
Grady Booch: I told Google to their face...what you need is some serious adult supervision. (2007 Turing lecture)
http:\\www.frankkerrigan.com
|
|
|
|
|
I am planning on using a CLR stored procedure to encapsulate information in a wrapper object and place this on an MSMQ private queue. My problem is that I can’t add System.Messaging as a reference. I understand that this restriction to ‘approved’ assemblies only can be worked around by creating my assembly as UNSAFE in SQL Server, but does this mean that I have to dynamically load System.Messaging?
|
|
|
|
|
how can i register oracle provider in debug
can any body help me plz.
and when ever i press f5, it says an error like unable to start debugging.
aslam
|
|
|
|
|
|
I have written a program to archive user reports to a temporary area and then deleting them after a set number of days.
This program works fine on our Local Area Network, but it cannot connect to an SQL Server which is in the DMZ. ASP.NET programs do not seem to have any trouble.
My program is written in C++ and Classic ADO.
My connection details are as follows: (written as VB as this is probably the most familiar)
Conn.Mode = adModeReadWrite OR adModeShareExclusive
Conn.CursorLocation = adUseClient
Conn.ConnectionTimeout = 15
Conn.CommandTimeout = 128
Conn.Provider = "sqloledb"
Conn.ConnectionString = "Data Source=TheServer;Initial Catalog=TheDB"
Conn.Attributes = 0;
Conn.IsolationLevel = adXactReadCommitted
Conn.Open( , UserNameVar, DBPasswordVar,adConnectUnspecified)
|
|
|
|
|
I'm using VB 2005, and sql databse.
I have a form with 4 textboxes: barcode_textbox, id_textbox, name_textbox, stock_textbox and they are connected to database table called "PRODUCTS"
Now when i add new record, and enter barcode that already exist in database, i want it to only add stock_textbox to that record in database!
How can i do that?
|
|
|
|
|
Do a check to see if the barcode exists, and if it does just update it. You can do this using a SELECT COUNT or IF EXISTS or whatever.
the last thing I want to see is some pasty-faced geek with skin so pale that it's almost translucent trying to bump parts with a partner - John Simmons / outlaw programmer
Deja View - the feeling that you've seen this post before.
|
|
|
|
|
i using Free SQLyog GUI v5.02 to save data for my asp.net web application. I need to addin rollback feature so while the system is fail during updating,adding and deleting data, the system can back to original(before perform the action).I am fresh for SQLyog, so have any setting at SQLyog for rollback or any idea or technique to perform this feature ? thanks ! 
Best regards,
Chee ken
|
|
|
|
|
No body reply me ?
Best regards,
Chee ken
|
|
|
|
|
Hi
I am programmatically creating SSIS package. I know to create db to db and db to flat file. I dont know how to create SSIS pacakge programmatically from Flat file to Destination DB. Could any one help me to solve my problem please.
kesavan
|
|
|
|
|
Help!
I have a table with a column named "Device Names" and a column named "Total Calls".
I want to COUNT these columns and display the top 10 devices according to the device names. How will I go about doing this?
Illegal Operation
Making Computer Software Talk
|
|
|
|
|
select top 10 DeviceName, sum(TotalCalls) SumCalls
from MyTable
group by DeviceName
order by 2
|
|
|
|
|
I want to wight a programm in (VB.net C#)for data transfer Client to Web Server; i have bulck data on my Client PC and i want to Upload all Data on Web Server less time consuming. (using C# code)?
Can somebody hepl me ????
please give me a right solution with well manar.
Pavan Pareta
|
|
|
|
|
Hi All,
I have been using full text search(Sql Server 2000)in my application for search purpose.
But on certain words i.e Noise words such as And,after,about i am receiving the following error:
Execution of a full-text operation failed.A clause of the query contained only ignored words.
I tried a lot to sort out this issue,but this is going to be out of my scope.
If anybody can help me then it would be really appreciable.
Thanks in Advance,
Vivek Rathore
|
|
|
|
|
Hello, I am using VB Net(2003). I’m asking questions, Namely: Here I have a data base under Access in the name of Invent.mdb, which contains 2 tables TreeviewItems and Products. The Products table has close to (1000) recordings and about (30) fields. On my (Form1) are TreeView, DataGrid and ListView. Operation Mode : At the starting, load the treeView with the TreeviewItems table, and on a node click of the TreeView it fills the Datagrid, and upon a new node click from the same Treeview it does empty the DataGrid and does re-fill it. It does that as long someone clicks on a treenode. Up to here all ok, I have a (2e) form (Form2) which opens on a click of button, this form contains Textboxes which are automatically fills (Data Binding of Data Grid Form1) here my question I would like to be able to add, modify, to remove recordings from my table Products, as I mentioned at the beginning, the Products table contained nearly 1000 recordings. thus Ref_ID column is label from 1 to 1000, assume to illustrate this example that DataGrid contains 32 recordings and which have for Ref_ID the reference label 110 to 142 and since that fields is the primary key which autoincriment . If I want to add a recording it will thus have the number 143 and at the time of the update how that will be done it cannot y have doubled blooms. See my dilemma, I hope to have been rather precise and that that someone will be kind enough to help me to see clearly. Thank you and waiting for advice.
|
|
|
|
|
andy38 wrote: thus have the number 143 and at the time of the update how that will be done it cannot y have doubled blooms
The ID should be set as an identity field in the database, when you create one, the data base should assign and return the ID. Your user should never enter it in, and it may not even be appropriate for them to see it or know it's there.
Christian Graus - C++ MVP
'Why don't we jump on a fad that hasn't already been widely discredited ?' - Dilbert
|
|
|
|
|
I have a question in regards to dynamically creating a cross tab query in sql server 2005. I have a table that contains the attributes of a product see below and I want to format is so that the outputs the product attributes to different columns with the corresponding values. I am using sql Server 2005 and have seen examples with using PIVOT function which does similar to what I want but they all the examples seem to use in circumstances where it is required to aggregate values in a column eg, sum a sales figure.
I could create a separate query for each attribute of a product and then use a join to get my result but this is not dynamic, i.e. if I add a new attribute I would have to change my query.
Can anyone help?
Before
Product Attribute AttributeValue
Apple Color Red
Celery Color Green
Banana Color Yellow
Apple Type Fruit
Celery Type Vege
After
Product Color Type
Apple Red Fruit
Celery Green Vege
Banana Yellow
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
