Insert Trigger - SQL Server Forums

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

Insert Trigger

Author

Topic

Delinda
Constraint Violating Yak Guru

315 Posts

Posted - 2008-05-18 : 02:35:28

I'm having table as below,
StudentEnroll_Thismonth
TransID(AutoNumber) | StudentID | Subject | TransDate
------------------------------------------------
1 | 0021 | A890 | 4/1/2008
2 | 0021 | A830 | 4/1/2008
3 | 0025 | A890 | 4/1/2008
4 | 0025 | A112 | 4/1/2008
5 | 0026 | A545 | 4/1/2008
.............
.............
99 | 0021 | A900 | 4/30/2008
100 | 0021 | A902 | 4/30/2008
101 | 0025 | A900 | 4/30/2008
*The table above contains what subject taken by student

Let's say, GETDATE()=5/1/2008
If execute Trans-SQL below,
INSERT INTO StudentEnroll_ThisMonth(StudentID,Subject,TransDate) values(0023,'B328',GETDATE())

How the trigger looks like to make sure the data in table shown as follow,
StudentEnroll_Thismonth
TransID(AutoNumber) | StudentID | Subject | TransDate
-----------------------------------------------------------
120 | 0023 | B328 | 5/1/2008
*All the previous month record move to StudentEnroll_PreviousMonth automatically

StudentEnroll_PreviousMonth
TransID(AutoNumber) | StudentID | Subject | TransDate
------------------------------------------------
..................
..................
..................
200 | 0021 | A890 | 4/1/2008
201 | 0021 | A830 | 4/1/2008
203 | 0025 | A890 | 4/1/2008
204 | 0025 | A112 | 4/1/2008
205 | 0026 | A545 | 4/1/2008
.................
.................
301 | 0021 | A900 | 4/30/2008
302 | 0021 | A902 | 4/30/2008
303 | 0025 | A900 | 4/30/2008

Anyone can help me to show the trigger?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-18 : 05:28:04

Whats purpose of this trigger? Are you sure that you will populating the data in StudentEnroll_Thismonth only during start of month, Else it will be a overhead each day because after first day when insert happens you wont have any records to be moved to prev month as all records will moved into previous month table on first insert itself. And trigger will keep on firing during all insert but wont have anything to do.

Delinda
Constraint Violating Yak Guru

315 Posts

Posted - 2008-05-18 : 06:08:55

What you say is right, trigger will keep on firing during all insert but wont have anyting to do.
StudentEnroll_ThisMonth storing current month data. It only firing 1st day of the month. May be i'm not good in design the application. My humble opinion, may be trigger is not the right solution. The right solution is stored procedure and executed by SQL Agent, is that right?

Actually i eagerly learn apply trigger in SQL Server 2005.

I'm creating table below,
StudentEnroll
TransID(AutoNumber) | StudentID | Subject | TransDate
------------------------------------------------
101 | 0021 | A890 | 4/1/2008
102 | 0021 | A830 | 4/1/2008
103 | 0025 | A890 | 4/1/2008
.....
.....
301 | 0022 | A880 | 5/17/2008
302 | 0021 | A700 | 5/17/2008
303 | 0022 | A766 | 5/17/2008

Below the summary table. I'm harcoded it.
StudentEnroll_Last7Days
TransID(AutoNumber) | StudentID | Subject | TransDate
------------------------------------------------
401 | 0021 | A890 | 5/11/2008
402 | 0021 | A830 | 5/11/2008
403 | 0025 | A890 | 5/11/2008
.....
.....
501 | 0022 | A880 | 5/17/2008
502 | 0021 | A700 | 5/17/2008
503 | 0022 | A766 | 5/17/2008

1. Let's say, GETDATE()= 5/18/2008.
2. Every insert into StudentEnroll, I want to maintain the StudentEnroll_Last7Days summary data depend on GETDATE().
3. So, i'm execute INSERT INTO StudentEnroll(StudentID,Subject,TransDate) values (0023,'B328',GETDATE())

Just want to learn, can show me the trigger in StudentEnroll to maintain dynamically StudentEnroll_Last7Days as follow

StudentEnroll_Last7Days
TransID(AutoNumber) | StudentID | Subject | TransDate
------------------------------------------------
460 | 0031 | A890 | 5/12/2008
461 | 0033 | A830 | 5/12/2008
462 | 0031 | A890 | 5/12/2008
.....
.....
601 | 0022 | A880 | 5/17/2008
602 | 0021 | A700 | 5/17/2008
603 | 0022 | A766 | 5/17/2008
604 | 0023 | B328 | 5/18/2008
*5/12/2008 to 5/18/2008 is 7 days
*You will see, data on 5/11/2008 is omit automatically in StudentEnroll_Last7Days
*You also see, new data insert into StudentEnroll_Last7Days

I just want to learn writing trigger.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-18 : 13:09:19

quote:
Originally posted by Delinda

What you say is right, trigger will keep on firing during all insert but wont have anyting to do.
StudentEnroll_ThisMonth storing current month data. It only firing 1st day of the month. May be i'm not good in design the application. My humble opinion, may be trigger is not the right solution. The right solution is stored procedure and executed by SQL Agent, is that right? Yup Exactly

Actually i eagerly learn apply trigger in SQL Server 2005.

I'm creating table below,
StudentEnroll
TransID(AutoNumber) | StudentID | Subject | TransDate
------------------------------------------------
101 | 0021 | A890 | 4/1/2008
102 | 0021 | A830 | 4/1/2008
103 | 0025 | A890 | 4/1/2008
.....
.....
301 | 0022 | A880 | 5/17/2008
302 | 0021 | A700 | 5/17/2008
303 | 0022 | A766 | 5/17/2008

Below the summary table. I'm harcoded it.
StudentEnroll_Last7Days
TransID(AutoNumber) | StudentID | Subject | TransDate
------------------------------------------------
401 | 0021 | A890 | 5/11/2008
402 | 0021 | A830 | 5/11/2008
403 | 0025 | A890 | 5/11/2008
.....
.....
501 | 0022 | A880 | 5/17/2008
502 | 0021 | A700 | 5/17/2008
503 | 0022 | A766 | 5/17/2008

1. Let's say, GETDATE()= 5/18/2008.
2. Every insert into StudentEnroll, I want to maintain the StudentEnroll_Last7Days summary data depend on GETDATE().
3. So, i'm execute INSERT INTO StudentEnroll(StudentID,Subject,TransDate) values (0023,'B328',GETDATE())

Just want to learn, can show me the trigger in StudentEnroll to maintain dynamically StudentEnroll_Last7Days as follow

StudentEnroll_Last7Days
TransID(AutoNumber) | StudentID | Subject | TransDate
------------------------------------------------
460 | 0031 | A890 | 5/12/2008
461 | 0033 | A830 | 5/12/2008
462 | 0031 | A890 | 5/12/2008
.....
.....
601 | 0022 | A880 | 5/17/2008
602 | 0021 | A700 | 5/17/2008
603 | 0022 | A766 | 5/17/2008
604 | 0023 | B328 | 5/18/2008
*5/12/2008 to 5/18/2008 is 7 days
*You will see, data on 5/11/2008 is omit automatically in StudentEnroll_Last7Days
*You also see, new data insert into StudentEnroll_Last7Days

I just want to learn writing trigger.

The trigger would be like this:-

CREATE TRIGGER YourTrigger 
ON StudentEnroll
AFTER INSERT 
AS
INSERT INTO StudentEnroll_Last7Days
SELECT fields
FROM StudentEnroll s
LEFT JOIN StudentEnroll_Last7Days b
ON b.StudentID=s.StudentID
AND b.Subject =s.Subject
AND b.TransDate = b.TransDate
WHERE s.TransDate>= DATEADD(dd,-7,GETDATE())
AND b.TransDate IS NULL
GO

Delinda
Constraint Violating Yak Guru

315 Posts

Posted - 2008-06-06 : 21:59:42

tq mr. visakh

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources