Author |
Topic |
Michael71
Posting Yak Master
126 Posts |
Posted - 2006-10-18 : 12:27:48
|
This display exactly what I want for my database and my form to pull out the information. How do I put this in a trigger.......USE InfoPathBudgetGOselect ReportID, ExpenseReportDate, monthlyExpenses, ytdExpenses = (select sum(monthlyExpenses) from TOSS x where x.ExpenseReportDate >= t.ExpenseReportDate)from TOSS torder by ExpenseReportDate |
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2006-10-18 : 12:35:44
|
Why do you want to use Select statement in Trigger?You need to use simple select or use Stored procedureMadhivananFailing to plan is Planning to fail |
|
|
Michael71
Posting Yak Master
126 Posts |
Posted - 2006-10-18 : 12:45:23
|
I meant change it to a Stored procedure or Trigger...Sorry |
|
|
snSQL
Master Smack Fu Yak Hacker
1837 Posts |
Posted - 2006-10-18 : 13:01:26
|
quote: I meant change it to a Stored procedure or Trigger
You should read up on the difference between the two!CREATE PROC dbo.YourProcASselect ReportID, ExpenseReportDate, monthlyExpenses,ytdExpenses = (select sum(monthlyExpenses) from InfoPathBudget.dbo.TOSS x where x.ExpenseReportDate >= t.ExpenseReportDate)from InfoPathBudget.dbo.TOSS torder by ExpenseReportDate |
|
|
Michael71
Posting Yak Master
126 Posts |
Posted - 2006-10-18 : 13:13:43
|
The databse did not sum up the data. I may need a trigger....Could you tell me how to write this as a trigger....and what is dbo |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2006-10-18 : 13:15:02
|
A SELECT statement does not do anything but grab the data. It will not update your data. If you want to update your data, then you need to run an UPDATE statement.Tara Kizer |
|
|
Michael71
Posting Yak Master
126 Posts |
Posted - 2006-10-18 : 13:16:56
|
Will this update statement...update it everytime I submit data to the database? |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2006-10-18 : 13:22:58
|
Yes it can. But you need to specify exactly what you want. Do you want the data updated each time you add rows to the table or update rows in the table?You also will need to show us exactly what you want. Show us sample rows that are being inserted and/or updated in the table that will be impacted by the trigger and what should the data look like after the trigger fires. This is also necessary in order to help you write the trigger. We can't just dream it up.Tara Kizer |
|
|
snSQL
Master Smack Fu Yak Hacker
1837 Posts |
Posted - 2006-10-18 : 13:30:21
|
I'd really recommend that you read this from cover to cover before asking more questions herehttp://www.amazon.com/Microsoft-SQL-Server-2000-Dummies/dp/0764507753Or go to the Books Online and read everything onSELECT, INSERT, UPDATE, DELETE, CREATE DATABASE, CREATE TABLE, CREATE VIEW, CREATE PROCEDURE, CREATE TRIGGER, CREATE FUNCTION(no really, I mean it, read it all and try all the examples)Also these sites which you were referred to before will help youhttp://www.sql-tutorial.net/ http://www.firstsql.com/tutor.htm http://www.w3schools.com/sql/default.asp |
|
|
Michael71
Posting Yak Master
126 Posts |
Posted - 2006-10-18 : 16:34:09
|
I got it to work. By using other means. Thanks for the advice. Another question, My Primary key...Report ID keeps on showing the same number when I open up the frontend form, should this key be incremented......Each time I save to the SQL database and open up with one number greater. What should I do to perform this? Thanks.Report ID201918171615..... |
|
|
snSQL
Master Smack Fu Yak Hacker
1837 Posts |
Posted - 2006-10-18 : 16:39:29
|
In the documentation you're reading on the CREATE TABLE statement you'll come across IDENTITY. Pay careful attention as you read that. |
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2006-10-18 : 17:01:18
|
quote: Originally posted by Michael71 I got it to work. By using other means. Thanks for the advice. Another question, My Primary key...Report ID keeps on showing the same number when I open up the frontend form, should this key be incremented......Each time I save to the SQL database and open up with one number greater. What should I do to perform this? Thanks.Report ID201918171615.....
If your Primary Key is actually repeated in the table, then it is not really a Primary Key, because duplicate primary keys are not allowed.If it is repeated in the output from your query, then you need to fix your query.CODO ERGO SUM |
|
|
|