trigger for log table - 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)

trigger for log table

Author

Topic

Havana
Starting Member

4 Posts

Posted - 2013-05-26 : 06:45:40

Hi,

I'm an IT student and currently we're learning how to work with SQL.

I want to fill in a log table on inserts, updates and deletes from another table.

In this log file I need to have:

LogID(anum),
Logdate(Getdate()),
user,
action (i,u,d),
The ID from the row in the other table,
Old values,
New values.

what I have now is this:


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE TRIGGER Logtabel_Insert ON TblBetalingen
AFTER INSERT
AS 
BEGIN
SET NOCOUNT ON
  
	INSERT INTO TblLog (Gebruiker, ExtraInfo, Actie, OudeWaarde, NieuweWaarde)
	Select Bediende, Bestelling,'Insert' from TblBestellingen 
	inner join TblBetalingen on TblBestellingen.BestelID = TblBetalingen.Bestelling 
	inner join TblLog on TblBetalingen.Bestelling = TblLog.ExtraInfo;	
	
AFTER UPDATE
AS
	
	INSERT INTO TblLog (Gebruiker, ExtraInfo, Actie, OudeWaarde, NieuweWaarde)
	Select Bediende, Bestelling,'Update' from TblBestellingen 
	inner join TblBetalingen on TblBestellingen.BestelID = TblBetalingen.Bestelling 
	inner join TblLog on TblBetalingen.Bestelling = TblLog.ExtraInfo;	
	
AFTER DELETE
AS
	
	INSERT INTO TblLog (Gebruiker, ExtraInfo, Actie, OudeWaarde, NieuweWaarde)
	Select Bediende, Bestelling,'Delete' from TblBestellingen 
	inner join TblBetalingen on TblBestellingen.BestelID = TblBetalingen.Bestelling 
	inner join TblLog on TblBetalingen.Bestelling = TblLog.ExtraInfo;	

END;
GO

I have no idea what so ever how to put Old value (OudeWaarde) and new value (NieuweWaarde) into the select statement...
The rest isn't working either, but I think a select statement could be the way to go...

However, I've tried several different ways, with insert into and values (but this only seems to work when you have preset words or values to fill in) I've tried working with select or set but I always seem to bump in to another problem...

Since I've been looking for a couple of hours now, I was wondering if you guys could give some tips as to the direction I should be thinking and looking in.

Thanks in advance! :)

Hanne

Havana

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-05-26 : 07:48:43

Couple of things:

1. There are two virtual tables - INSERTED and DELETED. Those will have the rows that have been inserted and deleted, respectively. An update operation is considered a DELETE of the existing rows followed by an INSERT of the updated rows. So both INSERTED and DELETED rows will have data for an update operation.

2. The syntax of what you wrote is not correct. You should either create 3 separate triggers, one each for insert, delete, and update (starting with the CREATE TRIGGER statement), or you should combine all into one using "FOR INSERT, UPDATE, DELETE".

3. There are some good examples on the MSDN page here. The documentation itself may look dense, but scroll down to the Examples section - they are pretty good. http://msdn.microsoft.com/en-us/library/ms189799.aspx

Havana
Starting Member

4 Posts

Posted - 2013-05-26 : 08:57:28

If I write it like this then:

 
CREATE TRIGGER Logtabel_Insert ON TblBetalingen
FOR INSERT, UPDATE, DELETE
AS 
BEGIN
SET NOCOUNT ON
  
	INSERT INTO TblLog (Gebruiker, ExtraInfo, Actie, OudeWaarde, NieuweWaarde)
	Select Bediende, Bestelling,'Insert' from TblBestellingen 
	inner join TblBetalingen on TblBestellingen.BestelID = TblBetalingen.Bestelling 
	inner join TblLog on TblBetalingen.Bestelling = TblLog.ExtraInfo;	
		

END;
GO

How can I say when to put insert, update or delete in the action column?

Or am i just wrong with the select statement?

I'm trying to use if exists now, although, I'm not quite sure how exactly that will work, but hopefully I'll find something online for that.

Havana

Havana
Starting Member

4 Posts

Posted - 2013-05-26 : 11:41:25

Hi,

just to ask, is this already more in the direction I should be working in:



SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER Logtabel_Insert
ON TblBetalingen
FOR INSERT, DELETE, UPDATE

AS
BEGIN
 IF EXISTS(SELECT * FROM Inserted where deleted is False)
 BEGIN
  INSERT INTO TblLog(Gebruiker, Actie, ExtraInfo, OudeWaarde, NieuweWaarde)
  SELECT Bediende, 'Insert', TblBetalingen.Bestelling,?,?
  FROM inserted
  inner join TblBestellingen on TblBestellingen.BestelID = inserted.Bestelling
  inner join TblBetalingen on TblBestellingen.BestelID = TblBetalingen.Bestelling
 END


 IF EXISTS(SELECT * FROM Deleted where inserted is False)
 BEGIN
  INSERT INTO TblLog(Gebruiker, Actie, ExtraInfo, OudeWaarde, NieuweWaarde)
  SELECT Bediende, 'Delete', TblBetalingen.Bestelling,?,?
  FROM Deleted
  inner join TblBestellingen on TblBestellingen.BestelID = deleted.Bestelling
  inner join TblBetalingen on TblBestellingen.BestelID = TblBetalingen.Bestelling
 END

END
GO

The question marks are still something I can't find, and also this is wrong: SELECT * FROM Deleted where inserted is False.

I am also wondering if I should write another part for de update part... I was thinking that an update is a delete of an old and an insert of a new row, so seems to me that that should be covered in those two parts, but, once again, I'm not sure, and google isn't helping me all that much.. :)

Could somebody give me some feedback please? My deadline is in a couple of hours...

*Just stressing a little* :p

Thanks alot!

Havana

Havana
Starting Member

4 Posts

Posted - 2013-05-26 : 11:45:01

quote:
Originally posted by Havana

Hi,

just to ask, is this already more in the direction I should be working in:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER Logtabel_Insert
ON TblBetalingen
FOR INSERT, DELETE, UPDATE

AS
BEGIN
IF EXISTS(SELECT * FROM Inserted where deleted is False)
BEGIN
INSERT INTO TblLog(Gebruiker, Actie, ExtraInfo, OudeWaarde, NieuweWaarde)
SELECT Bediende, 'Insert', TblBetalingen.Bestelling,?,?
FROM inserted
inner join TblBestellingen on TblBestellingen.BestelID = inserted.Bestelling
inner join TblBetalingen on TblBestellingen.BestelID = TblBetalingen.Bestelling
END

IF EXISTS(SELECT * FROM Deleted where inserted is False)
BEGIN
INSERT INTO TblLog(Gebruiker, Actie, ExtraInfo, OudeWaarde, NieuweWaarde)
SELECT Bediende, 'Delete', TblBetalingen.Bestelling,?,?
FROM Deleted
inner join TblBestellingen on TblBestellingen.BestelID = deleted.Bestelling
inner join TblBetalingen on TblBestellingen.BestelID = TblBetalingen.Bestelling
END

END
GO

The question marks are still something I can't find, and also this is wrong: SELECT * FROM Deleted where inserted is False.

I am also wondering if I should write another part for de update part... I was thinking that an update is a delete of an old and an insert of a new row, so seems to me that that should be covered in those two parts, but, once again, I'm not sure, and google isn't helping me all that much.. :)

Could somebody give me some feedback please? My deadline is in a couple of hours...

*Just stressing a little* :p

Thanks alot!

Havana

Sorry James, I just saw that you already said that an update is a delete and insert together.. stress is getting the best of me.. :)

Havana

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources