| Author |
 Topic  |
|
|
burzum
Yak Posting Veteran
55 Posts |
 Posted - 06/04/2012 : 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
India
47099 Posts |
Posted - 06/04/2012 : 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 - 06/04/2012 : 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
India
47099 Posts |
Posted - 06/04/2012 : 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 - 06/04/2012 : 18:40:19
|
| Fixed the problem. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47099 Posts |
Posted - 06/04/2012 : 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 - 06/05/2012 : 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
Flowing Fount of Yak Knowledge
3829 Posts |
Posted - 06/05/2012 : 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
India
47099 Posts |
Posted - 06/05/2012 : 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 - 06/05/2012 : 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
India
47099 Posts |
Posted - 06/05/2012 : 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/
|
|
|
| |
Topic |
|
Creating trigger for logs
