Creating a 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 Administration

Creating a trigger

Author

Topic

dtrivedi
Posting Yak Master

153 Posts

Posted - 2010-10-28 : 14:20:29

I come from a oracle background and work as a SQL DBA so i'm still learning. I was asked to Create a trigger so when a record is inserted into TBLPOSX00, it executes the stored procedure I wrote. Where do i start? Below is the stored procedure i wrote

CREATE procedure [dbo].[Auto_Finalize_Prev_Days] as
DELETE FROM TBLPOSX00 WHERE AUDIT_ENTERED_DATE < (DATEADD(DAY,-5,CAST(SYSDATETIME() AS DATE)));

UPDATE TBLPOSXE0 SET FINALIZED=1, FINALIZE_STAMP=SESSION_OPEN_TIME WHERE FINALIZED=0 AND AUDIT_ENTERED_DATE < (DATEADD(DAY,-5,CAST(SYSDATETIME() AS DATE)));
UPDATE TBLPOSXE0 SET SESSION_CLOSE_TIME = SESSION_OPEN_TIME WHERE FINALIZED=1 AND SESSION_CLOSE_TIME=0;

DELETE FROM TBLPOSXES WHERE AUDIT_ENTERED_DATE < (DATEADD(DAY,-5,CAST(SYSDATETIME() AS DATE)));

DELETE FROM TBLPOSXB0 WHERE AUDIT_ENTERED_DATE < (DATEADD(DAY,-5,CAST(SYSDATETIME() AS DATE)));

DELETE FROM TBLPOSXBS WHERE AUDIT_ENTERED_DATE < (DATEADD(DAY,-5,CAST(SYSDATETIME() AS DATE)));

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-11-03 : 09:51:00

Create an After INSERT trigger on the table TBLPOSX00 and execute the procedure inside it. But why do you want to do this whenever data are added to the table. It may cause some performance issues

Madhivanan

Failing to plan is Planning to fail

dtrivedi
Posting Yak Master

153 Posts

Posted - 2010-11-03 : 10:31:32

I don't understand what you are saying can you give me an example?
we want this as a test to see if something else that we need to put in to affect will work or not. it will only be temporary

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-11-03 : 10:35:35

quote:
Originally posted by dtrivedi

I don't understand what you are saying can you give me an example?
we want this as a test to see if something else that we need to put in to affect will work or not. it will only be temporary

Read about "Create trigger" in SQL Server help file

Madhivanan

Failing to plan is Planning to fail

dtrivedi
Posting Yak Master

153 Posts

Posted - 2010-11-03 : 11:01:55

so like this?

Create Trigger autofin
on TBLPOSX00
as
begin
CREATE procedure [dbo].[Auto_Finalize_Prev_Days] as
DELETE FROM TBLPOSX00 WHERE AUDIT_ENTERED_DATE < (DATEADD(DAY,-5,CAST(SYSDATETIME() AS DATE)));

UPDATE TBLPOSXE0 SET FINALIZED=1, FINALIZE_STAMP=SESSION_OPEN_TIME WHERE FINALIZED=0 AND AUDIT_ENTERED_DATE < (DATEADD(DAY,-5,CAST(SYSDATETIME() AS DATE)));
UPDATE TBLPOSXE0 SET SESSION_CLOSE_TIME = SESSION_OPEN_TIME WHERE FINALIZED=1 AND SESSION_CLOSE_TIME=0;

DELETE FROM TBLPOSXES WHERE AUDIT_ENTERED_DATE < (DATEADD(DAY,-5,CAST(SYSDATETIME() AS DATE)));

DELETE FROM TBLPOSXB0 WHERE AUDIT_ENTERED_DATE < (DATEADD(DAY,-5,CAST(SYSDATETIME() AS DATE)));

DELETE FROM TBLPOSXBS WHERE AUDIT_ENTERED_DATE < (DATEADD(DAY,-5,CAST(SYSDATETIME() AS DATE)));

End

dtrivedi
Posting Yak Master

153 Posts

Posted - 2010-11-03 : 11:03:15

forgot the insert

Create Trigger autofin
on TBLPOSX00
after insert
as
begin
CREATE procedure [dbo].[Auto_Finalize_Prev_Days] as
DELETE FROM TBLPOSX00 WHERE AUDIT_ENTERED_DATE < (DATEADD(DAY,-5,CAST(SYSDATETIME() AS DATE)));

UPDATE TBLPOSXE0 SET FINALIZED=1, FINALIZE_STAMP=SESSION_OPEN_TIME WHERE FINALIZED=0 AND AUDIT_ENTERED_DATE < (DATEADD(DAY,-5,CAST(SYSDATETIME() AS DATE)));
UPDATE TBLPOSXE0 SET SESSION_CLOSE_TIME = SESSION_OPEN_TIME WHERE FINALIZED=1 AND SESSION_CLOSE_TIME=0;

DELETE FROM TBLPOSXES WHERE AUDIT_ENTERED_DATE < (DATEADD(DAY,-5,CAST(SYSDATETIME() AS DATE)));

DELETE FROM TBLPOSXB0 WHERE AUDIT_ENTERED_DATE < (DATEADD(DAY,-5,CAST(SYSDATETIME() AS DATE)));

DELETE FROM TBLPOSXBS WHERE AUDIT_ENTERED_DATE < (DATEADD(DAY,-5,CAST(SYSDATETIME() AS DATE)));

End

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-11-04 : 05:38:33

quote:
Originally posted by dtrivedi

forgot the insert

Create Trigger autofin
on TBLPOSX00
after insert
as
begin
CREATE procedure [dbo].[Auto_Finalize_Prev_Days] as
DELETE FROM TBLPOSX00 WHERE AUDIT_ENTERED_DATE < (DATEADD(DAY,-5,CAST(SYSDATETIME() AS DATE)));

UPDATE TBLPOSXE0 SET FINALIZED=1, FINALIZE_STAMP=SESSION_OPEN_TIME WHERE FINALIZED=0 AND AUDIT_ENTERED_DATE < (DATEADD(DAY,-5,CAST(SYSDATETIME() AS DATE)));
UPDATE TBLPOSXE0 SET SESSION_CLOSE_TIME = SESSION_OPEN_TIME WHERE FINALIZED=1 AND SESSION_CLOSE_TIME=0;

DELETE FROM TBLPOSXES WHERE AUDIT_ENTERED_DATE < (DATEADD(DAY,-5,CAST(SYSDATETIME() AS DATE)));

DELETE FROM TBLPOSXB0 WHERE AUDIT_ENTERED_DATE < (DATEADD(DAY,-5,CAST(SYSDATETIME() AS DATE)));

DELETE FROM TBLPOSXBS WHERE AUDIT_ENTERED_DATE < (DATEADD(DAY,-5,CAST(SYSDATETIME() AS DATE)));

End

You need to remove this line

CREATE procedure [dbo].[Auto_Finalize_Prev_Days] as

Madhivanan

Failing to plan is Planning to fail

dtrivedi
Posting Yak Master

153 Posts

Posted - 2010-11-04 : 09:30:42

ok great thank you so much for your help! do you think you can help me with the other post I have up "csupport"??

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-11-08 : 04:36:11

quote:
Originally posted by dtrivedi

ok great thank you so much for your help! do you think you can help me with the other post I have up "csupport"??

You are welcome. Follow that thread

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources