Creating trigger for logs - 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

Creating trigger for logs

Author

Topic

burzum
Yak Posting Veteran

55 Posts

Posted - 2012-06-04 : 11:11:00

Hi, I'm running into this problem. I wrote a trigger for inserting to use it in my logs table it worked fine. But when I wrote similar trigger for delete I run into some problems. It does what I want, however when I remove the word from database it stores it as null in the logs table. Is there anyway to remove the table, but still keep track of it in the logs table? That's the whole point of the logs table.

Here's the code:

create TRIGGER supervisor_changes

on [Languages]

for delete

as 
declare @personedit varchar (50);
declare @wordedit varchar (50);

select @personedit=i.Person_Edit from inserted i;
select @wordedit=i.English_Words from inserted i;



begin

insert into [logs] (Person_Edit, word_edit, Action_ID) values (@personedit, @wordedit, 'A');

END

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-06-04 : 12:42:36

your trigger doesnt make any sense as inserted table wont have any values during delete operation. i think it should be


create TRIGGER supervisor_changes
on [Languages]
for delete
as 
begin

insert into [logs] (Person_Edit, word_edit, Action_ID)
select Person_Edit,English_Words,'A' from deleted

END

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

burzum
Yak Posting Veteran

55 Posts

Posted - 2012-06-04 : 16:02:34

I tried to run this procedure, however after the word has been removed nothing changes in the logs table. Any other suggestions?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-06-04 : 16:28:22

quote:
Originally posted by burzum

I tried to run this procedure, however after the word has been removed nothing changes in the logs table. Any other suggestions?

word is removed from where? are you trying to capture only changes to one of fields in table rather than whole record?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

burzum
Yak Posting Veteran

55 Posts

Posted - 2012-06-04 : 18:40:19

Fixed the problem.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-06-04 : 21:31:18

quote:
Originally posted by burzum

Fixed the problem.

can we see final solution?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

burzum
Yak Posting Veteran

55 Posts

Posted - 2012-06-05 : 11:48:59

Sure.

USE [Dynamic_Glossary]
GO

/****** Object:  Trigger [dbo].[supervisorDelete]    Script Date: 06/05/2012 08:48:10 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


CREATE TRIGGER [dbo].[supervisorDelete]

on [dbo].[Request_Words]

for delete

as 
declare @personedit varchar (50);
declare @wordedit varchar (50);

select @personedit=i.Person_Edit from deleted i;
select @wordedit=i.English_Word from deleted i;



begin

insert into [logs] (Person_Edit, word_edit,Action_ID) values (@personedit, @wordedit, 'D');

END

GO

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2012-06-05 : 12:00:55

You realize that will one handle a deletion of one row, right? If you delete more than one row, it will log a random row in your logs table.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-06-05 : 12:30:37

quote:

Originally posted by burzum

Sure.

USE [Dynamic_Glossary]
GO

/****** Object:  Trigger [dbo].[supervisorDelete]    Script Date: 06/05/2012 08:48:10 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


CREATE TRIGGER [dbo].[supervisorDelete]

on [dbo].[Request_Words]

for delete

as 
declare @personedit varchar (50);
declare @wordedit varchar (50);

select @personedit=i.Person_Edit from deleted i;
select @wordedit=i.English_Word from deleted i;



begin

insert into [logs] (Person_Edit, word_edit,Action_ID) values (@personedit, @wordedit, 'D');

END

GO

This will only work for single row deletes
see how i've used set based solution instead to handle multi row deletion

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

burzum
Yak Posting Veteran

55 Posts

Posted - 2012-06-05 : 14:57:21

I did not know that. Thank you for pointing out.

But in this case, I only allow the user to delete one row at a time. In the previous page they select a word from the list and in the next page they can delete it.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-06-05 : 16:13:20

quote:
Originally posted by burzum

I did not know that. Thank you for pointing out.

But in this case, I only allow the user to delete one row at a time. In the previous page they select a word from the list and in the next page they can delete it.

ok..then thats fine
make sure you dont have any other code which does batch deletion on table

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources