Trigger - one more question - nearly there

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 - one more question - nearly there

Author

Topic

Pinto
Aged Yak Warrior

590 Posts

Posted - 2008-04-25 : 09:26:13

What is wrong with this trigger and also how do i call it or pass the parameter to it.......or is there a dummy's tutorial out there somewhere....

CREATE TRIGGER [dbo].[spPHCR_InsertAuditReg]
-- Add the parameters for the stored procedure here

FOR INSERT, UPDATE, DELETE
@RegID numeric
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

-- Insert statements for procedure here
INSERT INTO tblPHCR_Register_Audit
SELECT * FROM tblPHCR_Register WHERE Reg_Id = @RegId

END

Pinto
Aged Yak Warrior

590 Posts

Posted - 2008-04-25 : 09:39:49

If i remove the parameter it works, but I need to pass in the parameter.......

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-25 : 09:41:25

Yes, the "dummy" is called Books Online, the SQL Server help file about every command available in SQL Server.

INSERT INTO tblPHCR_Register_Audit
SELECT x.* FROM tblPHCR_Register as x
inner join (select reg_id from inserted union select reg_id from deleted) as y on y.Reg_Id = x.Reg_Id

E 12°55'05.25"
N 56°04'39.16"

Pinto
Aged Yak Warrior

590 Posts

Posted - 2008-04-25 : 10:04:00

Great - thanks very much

Pinto
Aged Yak Warrior

590 Posts

Posted - 2008-04-25 : 10:08:59

Is there a way I can describe the action taken to make it appear in the audit table ? ie make a field the value Insert, Delete or Update accordingly ? I am using a table adapter to update my table

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-25 : 10:36:39

The real question is if you want to store both the old and new value for an update?

DECLARE	@Action VARCHAR(20)

IF EXISTS (SELECT * FROM inserted)
	IF EXISTS (SELECT * FROM deleted)
		SET	@Action = 'UPDATE'
	ELSE
		SET	@Action = 'INSERT'
ELSE
	SET	@Action = 'DELETE'


INSERT		tblPHCR_Register_Audit
SELECT		x.*,
		@Action
FROM		tblPHCR_Register AS x
INNER JOIN	(
			SELECT	reg_id
			FROM	inserted

			UNION

			SELECT	reg_id
			FROM	deleted
		) AS y ON y.Reg_Id = x.Reg_Id

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-25 : 10:40:48

[code]DECLARE @Action VARCHAR(20)

IF EXISTS (SELECT * FROM inserted)
IF EXISTS (SELECT * FROM deleted)
SET @Action = 'UPDATE'
ELSE
SET @Action = 'INSERT'
ELSE
SET @Action = 'DELETE'

INSERT tblPHCR_Register_Audit
SELECT y.ActionTaken,
x.*
FROM tblPHCR_Register AS x
INNER JOIN (
SELECT reg_id,
CASE
WHEN @Action = 'INSERT' THEN 'INSERT'
ELSE 'UPDATE (new value)'
END AS ActionTaken
FROM inserted

UNION ALL

SELECT reg_id,
CASE
WHEN @Action = 'UPDATE' THEN 'UPDATE (old value)'
ELSE 'DELETE'
END
FROM deleted
) AS y ON y.Reg_Id = x.Reg_Id[/code]

E 12°55'05.25"
N 56°04'39.16"

Pinto
Aged Yak Warrior

590 Posts

Posted - 2008-04-25 : 10:46:16

Many, many thanks. I will try this on Monday as here we are going home. Have a good weekend

Pinto
Aged Yak Warrior

590 Posts

Posted - 2008-04-28 : 04:38:02

Peso - thanks for your help. How do I get the value of @Action into my table ? Do I have to create a field called Action in my SQL table.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-04-28 : 04:59:07

quote:
Originally posted by Pinto

Peso - thanks for your help. How do I get the value of @Action into my table ? Do I have to create a field called Action in my SQL table.

Obviuosly you need a field if you have to store the value from variable into table.

Pinto
Aged Yak Warrior

590 Posts

Posted - 2008-04-28 : 05:09:26

I realised that wondered what the name had to be - I think maybe it should be Actiontaken looking at peso's 2nd example, but not sure about the first. This is a bit aadvanced for me....

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-04-28 : 05:12:39

Yup it is. The code alises the value to be ActionTaken field and it is used to insert into audit table.

Pinto
Aged Yak Warrior

590 Posts

Posted - 2008-04-28 : 05:23:31

I have added a field to both my tables Reg_ActionTaken and changed the trigger as below but only null gets written in the field

DECLARE @Action VARCHAR(20)

IF EXISTS (SELECT * FROM inserted)
IF EXISTS (SELECT * FROM deleted)
SET @Action = 'UPDATE'
ELSE
SET @Action = 'INSERT'
ELSE
SET @Action = 'DELETE'

INSERT tblPHCR_Register_Audit
SELECT y.Reg_ActionTaken,
x.*
FROM tblPHCR_Register AS x
INNER JOIN (
SELECT reg_id,
CASE
WHEN @Action = 'INSERT' THEN 'INSERT'
ELSE 'UPDATE (new value)'
END AS Reg_ActionTaken
FROM inserted

UNION ALL

SELECT reg_id,
CASE
WHEN @Action = 'UPDATE' THEN 'UPDATE (old value)'
ELSE 'DELETE'
END
FROM deleted
) AS y ON y.Reg_Id = x.Reg_Id

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-04-28 : 05:51:23

quote:
Originally posted by Pinto

I have added a field to both my tables Reg_ActionTaken and changed the trigger as below but only null gets written in the field

DECLARE @Action VARCHAR(20)

IF EXISTS (SELECT * FROM inserted)
BEGIN
IF EXISTS (SELECT * FROM deleted)
SET @Action = 'UPDATE'
ELSE
SET @Action = 'INSERT'
END
ELSE
SET @Action = 'DELETE'

INSERT tblPHCR_Register_Audit
SELECT y.Reg_ActionTaken,
x.*
FROM tblPHCR_Register AS x
INNER JOIN (
SELECT reg_id,
CASE
WHEN @Action = 'INSERT' THEN 'INSERT'
ELSE 'UPDATE (new value)'
END AS Reg_ActionTaken
FROM inserted

UNION ALL

SELECT reg_id,
CASE
WHEN @Action = 'UPDATE' THEN 'UPDATE (old value)'
ELSE 'DELETE'
END
FROM deleted
) AS y ON y.Reg_Id = x.Reg_Id

put a begin end

Pinto
Aged Yak Warrior

590 Posts

Posted - 2008-04-28 : 06:45:25

I am getting this error on Peso's second trigger

An explicit value for the identity column in table 'tblPHCR_Register_Audit' can only be specified when a column list is used and IDENTITY_INSERT is ON.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-28 : 06:59:55

It means you should not use * to get records and that you should explicít name columns to get.

E 12°55'05.25"
N 56°04'39.16"

Pinto
Aged Yak Warrior

590 Posts

Posted - 2008-04-28 : 07:34:56

But I have about 60 columns......

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-28 : 08:00:17

Yes, and what's your problem?
Because now you get duplicate column names and SQL Server will not get one at random for you.

E 12°55'05.25"
N 56°04'39.16"

Pinto
Aged Yak Warrior

590 Posts

Posted - 2008-04-28 : 08:46:54

Sorry - you've lost me now. Are you saying I cannot copy a record from one table to an audit trail table and have a field saying whether it was inserted, updated or deleted without typing out my 60 columns twice

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-28 : 09:03:42

Yes. But that is a one-time operation right?

E 12°55'05.25"
N 56°04'39.16"

Pinto
Aged Yak Warrior

590 Posts

Posted - 2008-04-28 : 09:13:00

Well, I suppose it will fill in my time this afternoon :-) Could you give me an example please for say two columns in your second example.For example where would I select Col1, Col2 and insert them. Unfortunately my column names are a lot longer than that....

DECLARE @Action VARCHAR(20)

IF EXISTS (SELECT * FROM inserted)
IF EXISTS (SELECT * FROM deleted)
SET @Action = 'UPDATE'
ELSE
SET @Action = 'INSERT'
ELSE
SET @Action = 'DELETE'

INSERT tblPHCR_Register_Audit
SELECT y.Reg_ActionTaken,
x.*
FROM tblPHCR_Register AS x
INNER JOIN (
SELECT reg_id,
CASE
WHEN @Action = 'INSERT' THEN 'INSERT'
ELSE 'UPDATE (new value)'
END AS Reg_ActionTaken
FROM inserted

UNION ALL

SELECT reg_id,
CASE
WHEN @Action = 'UPDATE' THEN 'UPDATE (old value)'
ELSE 'DELETE'
END
FROM deleted
) AS y ON y.Reg_Id = x.Reg_Id

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources