Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 How do I put this in a Trigger

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 InfoPathBudget
GO
select ReportID, ExpenseReportDate, monthlyExpenses,
ytdExpenses = (select sum(monthlyExpenses) from TOSS x where x.ExpenseReportDate >= t.ExpenseReportDate)
from TOSS t
order 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 procedure

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

Michael71
Posting Yak Master

126 Posts

Posted - 2006-10-18 : 12:45:23
I meant change it to a Stored procedure or Trigger...Sorry
Go to Top of Page

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.YourProc
AS
select ReportID, ExpenseReportDate, monthlyExpenses,
ytdExpenses = (select sum(monthlyExpenses) from InfoPathBudget.dbo.TOSS x where x.ExpenseReportDate >= t.ExpenseReportDate)
from InfoPathBudget.dbo.TOSS t
order by ExpenseReportDate
Go to Top of Page

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
Go to Top of Page

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
Go to Top of Page

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?
Go to Top of Page

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
Go to Top of Page

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 here
http://www.amazon.com/Microsoft-SQL-Server-2000-Dummies/dp/0764507753

Or go to the Books Online and read everything on
SELECT, 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 you
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp
Go to Top of Page

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 ID
20
19
18
17
16
15
.....
Go to Top of Page

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.
Go to Top of Page

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 ID
20
19
18
17
16
15
.....



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
Go to Top of Page
   

- Advertisement -