trigger with cursors - 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

trigger with cursors

Author

Topic

kvt.aravind
Starting Member

24 Posts

Posted - 2010-09-13 : 06:16:39

hi all,

USE [EEMSPROD]
GO
/****** Object: Trigger [dbo].[tata_travel_insert] Script Date: 09/13/2010 15:13:47 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
ALTER TRIGGER [dbo].[tata_travel_insert]
ON [dbo].[TATA_TRAVEL_OPENDELETESTATUS]
after INSERT
AS
BEGIN
SET NOCOUNT ON;

UPDATE [dbo].[TATA_TRAVEL_OPENDELETESTATUS] SET ARCHIVE_STATUS='N',Archived_on=getdate()

End

am using above trigger so that every time a row is inserted in tata_travel_opendeletestatus the columns will get updated . after few few minutes if i insert two new rows the date for previously inserted records (Archived_on) also getting updated with new date..So i need
a solution such that when a new book id is inserted the Archived_on date column for older records should not get updated

Aravind.T

Sachin.Nand

2937 Posts

Posted - 2010-09-13 : 06:41:47

Why are using a trigger?Cant you do the same in the Insert query which is inserting the data.
Something like

Insert into TATA_TRAVEL_OPENDELETESTATUS(...,ARCHIVE_STATUS,Archived_on)values(...,'N',getdate())

Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless.

PBUH

kvt.aravind
Starting Member

24 Posts

Posted - 2010-09-13 : 06:53:20

No yar the concept is that the table tata_travel_opendeletedstaus contains records from main table tata_travel_ticketinfo.
ie., the records which are older than 90 days IN TATA_TRAVEL_TICKETINFO are first moved to the tata_travel_opendeletedstatus and aLSO TABLES IN ARCHIVE DATABASE after that the records will get deleted in main table..
for this i have created stored procedure

TATA_TRAVEL_OPENDELETESTATUS CONTAINS FOuR columns from tata_travel_ticketinfo and two new columns archived_status and archived_on. if the records which are older are successfulyy moved to archive db and deleted in tata_travel_ticketinfo then archive_status should 'Y' and Archived_date should be the date when the record was deleted else Archive_status is 'N' After creating SP i have created above trigger every time the sp is executed all the records in tata_travel_opendeletestatus(even the records which got deleted one month before is getting update with new date in archived_on column) is getting updated with new date...kindly help

Sachin.Nand

2937 Posts

Posted - 2010-09-13 : 07:04:50

Do you have any column that uniquely identifies the rows in the table TATA_TRAVEL_OPENDELETESTATUS ?

Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless.

PBUH

kvt.aravind
Starting Member

24 Posts

Posted - 2010-09-13 : 07:08:02

YEs Activity_no in tata_travel_openstatusdeleted

Aravind.T

Sachin.Nand

2937 Posts

Posted - 2010-09-13 : 07:14:49

Change the trigger query to this


update T set ARCHIVE_STATUS='N',Archived_on=getdate()
from TATA_TRAVEL_OPENDELETESTATUS T inner join Inserted I on T.Activity_no =I.Activity_no

Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless.

PBUH

kvt.aravind
Starting Member

24 Posts

Posted - 2010-09-13 : 07:28:12

Thanks A lot...

You have provided the solution by single query...I have been trying with cursors ....

Any way thanks a lot for your solution...

Aravind.T

kvt.aravind
Starting Member

24 Posts

Posted - 2010-09-13 : 07:30:02

PBUH,
I am using trigger for first time i want to know about select * from inserted used triggers whether its like temp table or??

Aravind.T

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources