|
create trigger trgInsertDeposit
on Deposit --Deposit is table
for insert
as
declare @cAccountNumber char(15)
declre @mAmount money
declare @dDateDeposited datetime
if insert into Deposit values(@cAccountNumber ,@mAmount,@dDateDeposited )
begin
update Customer --customer is a table with column as mbalance
set mbalance = mbalance + insert.@mAmount where
cAccountNumber = @cAccountNumber
Print 'CUSTOMER BALANCE TABLE UPDATED'
Commit Tran
end
|
|
|
|
|
Rather than just say "What is wrong with this SQL code" and then paste the code, at least tell us the error message(s). I cannot tell from your code what it is supposed to do, so that also makes it difficult. Also, you don't mention the database product (there are subtle differences between each database product that may be important in solving problems)
From what I can see this is not even valid SQL code.
if insert into Deposit values(@cAccountNumber ,@mAmount,@dDateDeposited ) This is not a condition. You need a condition with an IF statement. In this case, a condition is something that will evaluate to true or false. An INSERT statement does not do this.
mbalance + insert.@mAmount That makes no sense what-so-ever. What is the insert doing there?
You do a COMMIT TRAN but is this really a wise thing to do inside a trigger?
Please don't take the following the wrong way; I am trying to help. There is so much that is wrong or not-understandable about this code that I cannot help you further. I would suggest that you find a good book on SQL and read it through (SQL Server Books On Line is a good reference, but you need a tutorial).
Do you want to know more?
WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and Forums
Upcoming talk:
SELECT UserName, Password FROM Users
-- Getting unauthorised access to a SQL Server, and how to prevent it.
|
|
|
|
|
Hi,
I'm new to VB.Net so I think I'm doing something totally wrong.
I have a form with several textboxes. They are bound to a dataset at designtime by setting the properties in de propertypanel.
txtVoornaam.Text=dsAdressen.Personen.Voornaam
When I search for a specific record in a table of the dataset using its PK, I find the record, but my textbox doesn't show the found value. I use this code
Dim dr as DataRow
dr = dsAdressen.Personen.Rows.Find(PK)
or
dsAdressen.Personen.FindByPersoonsID(PK)
I know the record is found, because with this code my textbox shows the right information
txtVoornaam.Text = dr("Voornaam")
Why do I have to use code like -- txtVoornaam.Text = dr("Voornaam") -- to show the found value in the textbox. The textbox databinding properties are set, so I expected that the right value should appear automatically.
Where am I going wrong??
Thanks,
 Hendrik Jan
|
|
|
|
|
Can anyone suggest me on what best possible option it could be to port data from MS-SQL to Oracle? Espacially for large databases and when I need to do this several times a day.
I am asking about best possible option as I alreay have few options and could not decide which one to adopt.
|
|
|
|
|
Hi Sharma,
Could you tell me what are the options, you have and how big is your database? Are you using any tools to migrate the data? Which version of SQL and which version of Oracle? I may help you in this..
Thanks
Srinivas
|
|
|
|
|
Oracle has a SQL migration tool to port over from MSSQL. See the Oracle TechNet site.
onwards and upwards...
|
|
|
|
|
dear all:
I'm trying to use OLE DB to access the SQL Server instead of ODBC(MFC) which I was quite familar. Here is some simple codes I used to open data source:
CString m_username,m_password;
...
...
CDBProSet m_dbproset;
m_dbproset.AddProperty(DBPROP_INIT_DATASOURCE,L"SQL_SERVER_HOSTNAME");
m_dbproset.AddProperty(DBPROP_AUTH_USERID,m_username);
m_dbproset.AddProperty(DBPROP_AUTH_PASSWORD,m_password);
m_dbproset.AddProperty(DBPROP_INIT_CATALOG,L"DEFAULT_DATABASE");
CDataSource m_datasource;
m_datasource.Open(L"SQLOLEDB",&m_dbproset,1);
When I call CDataSource::Open menthod I was informed that some OLE DB process make mistake.
Can anyone tell me is there anything wrong with my codes or I missed sth?
Thank you very much.
Lisoft
|
|
|
|
|
Maybe if you posted the actual error message someone could help...
Anger is the most impotent of passions. It effects nothing it goes about, and hurts the one who is possessed by it more than the one against whom it is directed.
Carl Sandburg
|
|
|
|
|
As we known,we can get record set like the following:
_RecordsetPtr pRst = pCmd.Execute(0,0,"ss");//pCmd is a ADO Command object,"ss" is the name of stored procedure to be executed.
and get return value or output parameter after call pRst->Close();
but when the stored procedure has no recordset,pRst will be a closed Recordset,it can't be close again.This time I can't get the return value or output value,Why?
You can send mail to me discussing this problem:
xiaolinzi0941@hotmail.com,thanks!
|
|
|
|
|
Hi there,
I'm trying to use ADO in my app. Here is an example query:
SELECT [Translation] FROM TRANSLATED_OTHER WHERE [Language] = 'Chinese' AND Text = 'Length';
If I call Fields::GetItem("Translation") on my RecordSet I get back the correct value. However if I use Fields::GetItem(0) a _com_error is thrown and I get the following error:
Item cannot be found in the collection corresponding to the requested name or ordinal.
What am I missing?
Thank you.
|
|
|
|
|
I don't quite remember but it may be that Item index in Recordset starts from 1 instead of 0.
Try using Fields::GetItem(1) instead.
Edbert
Edbert P.
Sydney, Australia.
|
|
|
|
|
Thank you for the suggestion, unfortuantely I already tried that to no avail.
|
|
|
|
|
Carlos Antollini posted a nice aricle with a wrapper class for ADO
here[^]. His wrapper method for Fields.GetItem(int index) worked well for me. Perhaps inspecting his code would answer your question.
Anger is the most impotent of passions. It effects nothing it goes about, and hurts the one who is possessed by it more than the one against whom it is directed.
Carl Sandburg
|
|
|
|
|
Thanks, I'll take a look at it.
|
|
|
|
|
I've got rather a lot of cleaned data that has the title,first and last names stored as a single string in one column - is there a simplish way to split this out into 3 columns. e.g something like this ?
select splitTitle(name) as title, splitFirst(name) as first, splitLast(name) as last from myTable
Also what books do people recommend for learning this kind of stuff ?
Many thanks for any help
|
|
|
|
|
Hi Jerry,
You can split the values using substring and charindex in SQL Server. I guess, you have to write a stored procedure to split them based on delimiter by passing concatinated string and delimiter and get title, first and last names. I can help you in this, if you try out something..
Thanks,
Srinivas
|
|
|
|
|
Anyone know of a free/cheap tool that will connect to SQL server and output a simple report showing the tables and columns (including name, type, size and description)? Choosing the tables would be nice, as would the ability to list indexes.
cheers,
Chris Maunder
|
|
|
|
|
|
Looks good! Can't wait for the full article 
cheers,
Chris Maunder
|
|
|
|
|
|
Sweet!!
So hold onto that 5 I just voted you, and when the article's done you can transfer it over to the full article 
This is really awesome Colin. Thanks
cheers,
Chris Maunder
|
|
|
|
|
How I can rename SQL server name in a machine?
thanks.
|
|
|
|
|
|
|
I have an application which reads large CSV files and displays them in a DataGrid.
I have many methods for updating the DataTable EG Add/Delete/Rename/Fill Columns along with Math Functions EG Divide/Multiply ETC.
The question I have is where should the logic reside for updating the table? What object/s or class/es should be responsible for performing the upadte?
At the moment, I have several forms for displaying the options associated with the relevant functions and pass the DataTable to peform the update but this is getting messy.
I want to re-structure the application before it gets to big and at the same time seperate the data from the interface so the data source becomes transparent to the front end.
Is this possible and what is the best approach?
Regards
Wayne Phipps
____________
Time is the greatest teacher... unfortunately, it kills all of its students
LearnVisualStudio.Net
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
