Tracking the deleted records - 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

SQL Server 2008 Forums

Transact-SQL (2008)

Tracking the deleted records

Author

Topic

kiranmurali
Yak Posting Veteran

55 Posts

Posted - 2011-03-11 : 04:24:09

Hi all,

This is a senario:

i have some master tables and userdefined tables the master table fields are reffered to the userdefined tables.

1. when ever the record is deleted from the master table or user defined tables it should delete all the dependencies also.
2. i have to track all the deleted records

can any one help me in solving this.

Thanks in advance,
kiranmayee

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2011-03-11 : 04:26:51

You'll need some archive tables and at least one trigger per table.

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2011-03-11 : 06:15:31

For #1 you should do this manually by issuing individual delete statements. Using cascade deletes is regarded bad practice and should be avoided.

For #2 you can use the audit-scripts I posted in the script library a while back. It will do quite a bit more than what you are asking for but I'm sure you can make it fit your needs:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=141844

- Lumbago
My blog-> http://thefirstsql.com/2011/02/07/regular-expressions-advanced-string-matching-and-new-split-function-sql-server-2008-r2/

kiranmurali
Yak Posting Veteran

55 Posts

Posted - 2011-03-16 : 03:46:43

whenever am running the script that i got from the link for master table it is throwing the constraint error (unique constarint error).
is there any modifications that i have to do to the script in the link. pls do let me know.

Regards,
Kiranmayee

Ranjit.ileni
Posting Yak Master

183 Posts

Posted - 2011-03-16 : 05:35:18

I handle above error by adding extraname for NameofConstraint as below
in the stored procedure

WHEN 'UQ' THEN name +'_DataVersion'+ ' UNIQUE '

Lumbago ,Please let me know if it is wrong

--Ranjit

kiranmurali
Yak Posting Veteran

55 Posts

Posted - 2011-03-16 : 06:48:06

Thanks a lot, it is working fine.

Ranjit.ileni
Posting Yak Master

183 Posts

Posted - 2011-03-16 : 07:09:45

Welcome :)

--Ranjit

kiranmurali
Yak Posting Veteran

55 Posts

Posted - 2011-04-06 : 07:21:41

Hi,

this is the script of one of my table, while running the script for audit table. it is giving error.

Msg 102, Level 15, State 1, Line 14
Incorrect syntax near ')'..

this is the table script..

CREATE TABLE [dbo].[PNET_EMP_EXIT1](
[EMPEXITID] [int] IDENTITY(1,1) NOT NULL,
[EMPID] [int] NOT NULL,
[DATE_RELIEVING] [date] NOT NULL,
[DATE_RESIGNATION] [date] NOT NULL,
[MANAGERID] [int] NOT NULL,
[REASON_LEAVINGID] [int] NOT NULL,
[DESCRIPTION] [varchar](250) NOT NULL,
[NEGOTIATION_DISCUSSION] [varchar](250) NULL,
[RESIGNATION_LETTER] [varbinary](max) FILESTREAM NULL,
[RowGuid] [uniqueidentifier] ROWGUIDCOL NOT NULL,
[STATUSID] [int] NOT NULL,
[EXPERIENCE_LETTER] [varbinary](max) FILESTREAM NULL,
[MANAGER_COMMENTS] [varchar](max) NULL,
[DEPARTMENTID] [int] NOT NULL,
[APPROVED_DATE] [date] NULL,
[HR_COMMENTS] [varchar](250) NULL,
[IMMIDIATE_MGRID] [int] NOT NULL,
[INITIATED_DATE] [date] NULL,

PRIMARY KEY CLUSTERED
(
[EMPEXITID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] FILESTREAM_ON [pp_on_net_itss_prod_db_FileStreamGroup1],
UNIQUE NONCLUSTERED
(
[RowGuid] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] FILESTREAM_ON [pp_on_net_itss_prod_db_FileStreamGroup1]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[PNET_EMP_EXIT1] WITH CHECK ADD FOREIGN KEY([DEPARTMENTID])
REFERENCES [dbo].[PNET_MASTER_FUNCTION_DEPARTMENT] ([FUNCTION_DEPARTMENTID])
GO

ALTER TABLE [dbo].[PNET_EMP_EXIT1] WITH CHECK ADD FOREIGN KEY([EMPID])
REFERENCES [dbo].[PNET_PFILE_DATATABLE1] ([PI_EmployeeId])
GO

ALTER TABLE [dbo].[PNET_EMP_EXIT1] WITH CHECK ADD FOREIGN KEY([IMMIDIATE_MGRID])
REFERENCES [dbo].[PNET_PFILE_DATATABLE1] ([PI_EmployeeId])
GO

ALTER TABLE [dbo].[PNET_EMP_EXIT1] WITH CHECK ADD FOREIGN KEY([MANAGERID])
REFERENCES [dbo].[PNET_PFILE_DATATABLE1] ([PI_EmployeeId])
GO

ALTER TABLE [dbo].[PNET_EMP_EXIT1] WITH CHECK ADD FOREIGN KEY([REASON_LEAVINGID])
REFERENCES [dbo].[PNET_EMP_MASTER_LEAVINGREASONS] ([LEAVINGREASONID])
GO

ALTER TABLE [dbo].[PNET_EMP_EXIT1] WITH CHECK ADD FOREIGN KEY([STATUSID])
REFERENCES [dbo].[PNET_EMP_MASTER_STATUS] ([STATUSID])
GO

ALTER TABLE [dbo].[PNET_EMP_EXIT1] ADD DEFAULT (newid()) FOR [RowGuid]
GO

I am not able to trace why it is throwing this error.

can any one help me to solve this problem.

Thanks in Advance,
Kiranmayee

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources