Trigger prob. when trying to have several statemen

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 prob. when trying to have several statemen

Author

Topic

kfluffie
Posting Yak Master

103 Posts

Posted - 2009-01-25 : 22:20:02

Hello!
I am having problem with a trigger. This is how it looks like now:

CREATE TRIGGER test_personal
ON dbo.personal
FOR UPDATE, INSERT, DELETE
AS

INSERT INTO dbo.loggning
(user_id, tabell, statement, tidpunkt, innan_varde, ny_varde)
SELECT 
	suser_sname(), 
	'Personal',
	'TEST', 
	GetDate(), 
	(
		'Chef_id: ' + CAST(DELETED.chef_id as varchar(3)) + 
		', lonakat_id: ' + CAST(DELETED.lonekat_id as varchar(3)) + 
		', fornamn: ' + LTRIM(RTRIM(DELETED.fornamn)) +
		', efternamn: ' + LTRIM(RTRIM(DELETED.efternamn)) + 
		', titel: ' + LTRIM(RTRIM(DELETED.titel))
	),
	(
		'Chef_id: ' + CAST(INSERTED.chef_id as varchar(3)) + 
		', lonakat_id: ' + CAST(INSERTED.lonekat_id as varchar(3)) + 
		', fornamn: ' + LTRIM(RTRIM(INSERTED.fornamn)) +
		', efternamn: ' + LTRIM(RTRIM(INSERTED.efternamn)) + 
		', titel: ' + LTRIM(RTRIM(INSERTED.titel))
	)
FROM INSERTED, DELETED

When I process an UPDATE, INSERT and DELETE it is only UPDATE which is logged.

This is how the DELETE trigger looked before:

CREATE TRIGGER delete_personal
ON dbo.personal
FOR DELETE
AS

INSERT INTO dbo.loggning
(user_id, tabell, statement, tidpunkt, innan_varde, ny_varde)
SELECT 
	user, 
	'Personal',
	'DELETE', 
	GetDate(), 
	(
		'Chef_id: ' + CAST(DELETED.chef_id as varchar(3)) + 
		', lonakat_id: ' + CAST(DELETED.lonekat_id as varchar(3)) + 
		', fornamn: ' + LTRIM(RTRIM(DELETED.fornamn)) +
		', efternamn: ' + LTRIM(RTRIM(DELETED.efternamn)) + 
		', titel: ' + LTRIM(RTRIM(DELETED.titel))
	),
	NULL -- Finns inget ny_varde
FROM DELETED

But I want to have all three statemens in one.

Any suggestion what the problem might be?

Best Regards,
KFluffie

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-01-25 : 22:25:27

It's because you have deleted and inserted in the select part. Those tables will only have rows in it in an UPDATE statement. For inserts, only the inserted table will have rows. For deletes, only the deleted tables will have rows.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

nr
SQLTeam MVY

12543 Posts

Posted - 2009-01-25 : 22:28:23

from inserted, deleted

This is a cartesian join - you could be in big trouble if someone updated multiple rows.

NSERT INTO dbo.loggning
(user_id, tabell, statement, tidpunkt, innan_varde, ny_varde)
SELECT
suser_sname(),
'Personal',
'TEST',
GetDate(),
(
'Chef_id: ' + CAST(DELETED.chef_id as varchar(3)) +
', lonakat_id: ' + CAST(DELETED.lonekat_id as varchar(3)) +
', fornamn: ' + LTRIM(RTRIM(DELETED.fornamn)) +
', efternamn: ' + LTRIM(RTRIM(DELETED.efternamn)) +
', titel: ' + LTRIM(RTRIM(DELETED.titel))
),
(
'Chef_id: ' + CAST(INSERTED.chef_id as varchar(3)) +
', lonakat_id: ' + CAST(INSERTED.lonekat_id as varchar(3)) +
', fornamn: ' + LTRIM(RTRIM(INSERTED.fornamn)) +
', efternamn: ' + LTRIM(RTRIM(INSERTED.efternamn)) +
', titel: ' + LTRIM(RTRIM(INSERTED.titel))
)
FROM INSERTED
full outer join DELETED
on INSERTED.pk = DELETED.pk

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

kfluffie
Posting Yak Master

103 Posts

Posted - 2009-01-26 : 07:12:06

quote:
Originally posted by tkizer

It's because you have deleted and inserted in the select part. Those tables will only have rows in it in an UPDATE statement. For inserts, only the inserted table will have rows. For deletes, only the deleted tables will have rows.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Hi!
Thanks both for your help.

I changed the trigger to this:

CREATE TRIGGER test_personal
ON dbo.personal
FOR INSERT, DELETE, UPDATE
AS



INSERT INTO dbo.loggning
(user_id, tabell, statement, tidpunkt, innan_varde, ny_varde)
SELECT 
	user, 
	'Personal',
	'UPDATE', 
	GetDate(), 
	(
		'Chef_id: ' + CAST(DELETED.chef_id as varchar(3)) + 
		', lonakat_id: ' + CAST(DELETED.lonekat_id as varchar(3)) + 
		', fornamn: ' + LTRIM(RTRIM(DELETED.fornamn)) +
		', efternamn: ' + LTRIM(RTRIM(DELETED.efternamn)) + 
		', titel: ' + LTRIM(RTRIM(DELETED.titel))
	),
	(
		'Chef_id: ' + CAST(INSERTED.chef_id as varchar(3)) + 
		', lonakat_id: ' + CAST(INSERTED.lonekat_id as varchar(3)) + 
		', fornamn: ' + LTRIM(RTRIM(INSERTED.fornamn)) +
		', efternamn: ' + LTRIM(RTRIM(INSERTED.efternamn)) + 
		', titel: ' + LTRIM(RTRIM(INSERTED.titel))
	)
FROM INSERTED
full outer join DELETED
on INSERTED.personal_id = DELETED.personal_id



INSERT INTO dbo.loggning
(user_id, tabell, statement, tidpunkt, innan_varde, ny_varde)
SELECT 
	user, 
	'Personal',
	'DELETED', 
	GetDate(), 
	(
		'Chef_id: ' + CAST(DELETED.chef_id as varchar(3)) + 
		', lonakat_id: ' + CAST(DELETED.lonekat_id as varchar(3)) + 
		', fornamn: ' + LTRIM(RTRIM(DELETED.fornamn)) +
		', efternamn: ' + LTRIM(RTRIM(DELETED.efternamn)) + 
		', titel: ' + LTRIM(RTRIM(DELETED.titel))
	),
	NULL -- Finns inget ny_varde
FROM DELETED


INSERT INTO dbo.loggning
(user_id, tabell, statement, tidpunkt, innan_varde, ny_varde)
SELECT 
	user, 
	'Personal',
	'INSERTED', 
	GetDate(), 
	NULL, -- Finns inget innan_varde
	(
		'Chef_id: ' + CAST(INSERTED.chef_id as varchar(3)) + 
		', lonakat_id: ' + CAST(INSERTED.lonekat_id as varchar(3)) + 
		', fornamn: ' + LTRIM(RTRIM(INSERTED.fornamn)) +
		', efternamn: ' + LTRIM(RTRIM(INSERTED.efternamn)) + 
		', titel: ' + LTRIM(RTRIM(INSERTED.titel))
	)
FROM INSERTED

However, when I use an UPDATE it fulfills all the criteria. I don't really now how to make SQL only choose one of the "INSERT"-statements.

Any suggestion is highly appreciated.

Best Regards,
Tomas

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-01-26 : 07:20:06

You can make your own trigger for each case INSERT/UPDATE/DELETE.

Webfred

No, you're never too old to Yak'n'Roll if you're too young to die.

kfluffie
Posting Yak Master

103 Posts

Posted - 2009-01-26 : 07:46:47

quote:
Originally posted by webfred

You can make your own trigger for each case INSERT/UPDATE/DELETE.

Webfred

No, you're never too old to Yak'n'Roll if you're too young to die.

Hi Webfred!
That is what I first did, but however, this is a school project and it I should do a maximum three triggers which checks for records changes for three tables:
"Create a trigger for each table that records changes in the tables, personal, utbetalning and lonekat (ie a total of three triggers) and store them in a separate logging table."

Best Regards,
KF

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-01-26 : 07:54:52

Then you can do something like this to control the trigger:

DECLARE @inserted_count int
DECLARE @deleted_count int
DECLARE @Action char
SELECT @inserted_count= COUNT(*) FROM inserted
SELECT @deleted_count= COUNT(*) FROM deleted
IF ((@inserted_count > 0) AND (@deleted_count > 0))
BEGIN
    SELECT @Action = 'U'
END
ELSE
BEGIN
    IF (@inserted_count > 0)
        SELECT @Action = 'I'
    ELSE
        SELECT @Action = 'D'
END
IF (@Action = 'I')
BEGIN
...
END
IF (@Action = 'U')
BEGIN
...
END
IF (@Action = 'D')
BEGIN
...
END

Webfred

No, you're never too old to Yak'n'Roll if you're too young to die.

kfluffie
Posting Yak Master

103 Posts

Posted - 2009-01-26 : 08:06:00

quote:

Originally posted by webfred

Then you can do something like this to control the trigger:

DECLARE @inserted_count int
DECLARE @deleted_count int
DECLARE @Action char
SELECT @inserted_count= COUNT(*) FROM inserted
SELECT @deleted_count= COUNT(*) FROM deleted
IF ((@inserted_count > 0) AND (@deleted_count > 0))
BEGIN
    SELECT @Action = 'U'
END
ELSE
BEGIN
    IF (@inserted_count > 0)
        SELECT @Action = 'I'
    ELSE
        SELECT @Action = 'D'
END
IF (@Action = 'I')
BEGIN
...
END
IF (@Action = 'U')
BEGIN
...
END
IF (@Action = 'D')
BEGIN
...
END

Webfred

No, you're never too old to Yak'n'Roll if you're too young to die.

Thanks!
It works like a charm!

CREATE TRIGGER personal_record_changes
ON dbo.personal
FOR INSERT, DELETE, UPDATE
AS

declare @inserted_count int
declare @deleted_count int
declare @action char(1)

SELECT @inserted_count = COUNT(*) FROM INSERTED
SELECT @deleted_count = COUNT(*) FROM DELETED

IF((@inserted_count > 0) AND (@deleted_count > 0))
BEGIN
	SELECT @action = 'U'
END

ELSE
BEGIN
	IF(@inserted_count > 0)
		SELECT @action = 'I'
	ELSE
		SELECT @action = 'D'
END


IF(@action = 'I')
BEGIN
INSERT INTO dbo.loggning
(user_id, tabell, statement, tidpunkt, innan_varde, ny_varde)
SELECT 
	user, 
	'Personal',
	'INSERTED', 
	GetDate(), 
	NULL, -- Finns inget innan_varde
	(
		'Chef_id: ' + CAST(INSERTED.chef_id as varchar(3)) + 
		', lonakat_id: ' + CAST(INSERTED.lonekat_id as varchar(3)) + 
		', fornamn: ' + LTRIM(RTRIM(INSERTED.fornamn)) +
		', efternamn: ' + LTRIM(RTRIM(INSERTED.efternamn)) + 
		', titel: ' + LTRIM(RTRIM(INSERTED.titel))
	)
FROM INSERTED
END


IF(@action = 'D')
BEGIN
INSERT INTO dbo.loggning
(user_id, tabell, statement, tidpunkt, innan_varde, ny_varde)
SELECT 
	user, 
	'Personal',
	'DELETED', 
	GetDate(), 
	(
		'Chef_id: ' + CAST(DELETED.chef_id as varchar(3)) + 
		', lonakat_id: ' + CAST(DELETED.lonekat_id as varchar(3)) + 
		', fornamn: ' + LTRIM(RTRIM(DELETED.fornamn)) +
		', efternamn: ' + LTRIM(RTRIM(DELETED.efternamn)) + 
		', titel: ' + LTRIM(RTRIM(DELETED.titel))
	),
	NULL -- Finns inget ny_varde
FROM DELETED
END


IF(@action = 'U')
BEGIN
INSERT INTO dbo.loggning
(user_id, tabell, statement, tidpunkt, innan_varde, ny_varde)
SELECT 
	user, 
	'Personal',
	'UPDATE', 
	GetDate(), 
	(
		'Chef_id: ' + CAST(DELETED.chef_id as varchar(3)) + 
		', lonakat_id: ' + CAST(DELETED.lonekat_id as varchar(3)) + 
		', fornamn: ' + LTRIM(RTRIM(DELETED.fornamn)) +
		', efternamn: ' + LTRIM(RTRIM(DELETED.efternamn)) + 
		', titel: ' + LTRIM(RTRIM(DELETED.titel))
	),
	(
		'Chef_id: ' + CAST(INSERTED.chef_id as varchar(3)) + 
		', lonakat_id: ' + CAST(INSERTED.lonekat_id as varchar(3)) + 
		', fornamn: ' + LTRIM(RTRIM(INSERTED.fornamn)) +
		', efternamn: ' + LTRIM(RTRIM(INSERTED.efternamn)) + 
		', titel: ' + LTRIM(RTRIM(INSERTED.titel))
	)
FROM INSERTED
full outer join DELETED
on INSERTED.personal_id = DELETED.personal_id
END

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-26 : 08:17:50

wont this be enough?


CREATE TRIGGER personal_record_changes
ON dbo.personal
FOR INSERT, DELETE, UPDATE
AS
BEGIN

INSERT INTO dbo.loggning
(user_id, tabell, statement, tidpunkt, innan_varde, ny_varde)
SELECT 
	user, 
	'Personal',
	CASE WHEN INSERTED.personal_id IS NOT NULL
             AND DELETED.personal_id IS NOT NULL THEN'UPDATE'
             WHEN INSERTED.personal_id IS NOT NULL
             AND DELETED.personal_id IS NULL THEN'INSERT'
             ELSE 'DELETE'
        END, 
	GetDate(), 
	(
		'Chef_id: ' + CAST(DELETED.chef_id as varchar(3)) + 
		', lonakat_id: ' + CAST(DELETED.lonekat_id as varchar(3)) + 
		', fornamn: ' + LTRIM(RTRIM(DELETED.fornamn)) +
		', efternamn: ' + LTRIM(RTRIM(DELETED.efternamn)) + 
		', titel: ' + LTRIM(RTRIM(DELETED.titel))
	),
	(
		'Chef_id: ' + CAST(INSERTED.chef_id as varchar(3)) + 
		', lonakat_id: ' + CAST(INSERTED.lonekat_id as varchar(3)) + 
		', fornamn: ' + LTRIM(RTRIM(INSERTED.fornamn)) +
		', efternamn: ' + LTRIM(RTRIM(INSERTED.efternamn)) + 
		', titel: ' + LTRIM(RTRIM(INSERTED.titel))
	)
FROM INSERTED
full outer join DELETED
on INSERTED.personal_id = DELETED.personal_id
END

kfluffie
Posting Yak Master

103 Posts

Posted - 2009-01-26 : 08:45:05

quote:

Originally posted by visakh16

wont this be enough?


CREATE TRIGGER personal_record_changes
ON dbo.personal
FOR INSERT, DELETE, UPDATE
AS
BEGIN

INSERT INTO dbo.loggning
(user_id, tabell, statement, tidpunkt, innan_varde, ny_varde)
SELECT 
	user, 
	'Personal',
	CASE WHEN INSERTED.personal_id IS NOT NULL
             AND DELETED.personal_id IS NOT NULL THEN'UPDATE'
             WHEN INSERTED.personal_id IS NOT NULL
             AND DELETED.personal_id IS NULL THEN'INSERT'
             ELSE 'DELETE'
        END, 
	GetDate(), 
	(
		'Chef_id: ' + CAST(DELETED.chef_id as varchar(3)) + 
		', lonakat_id: ' + CAST(DELETED.lonekat_id as varchar(3)) + 
		', fornamn: ' + LTRIM(RTRIM(DELETED.fornamn)) +
		', efternamn: ' + LTRIM(RTRIM(DELETED.efternamn)) + 
		', titel: ' + LTRIM(RTRIM(DELETED.titel))
	),
	(
		'Chef_id: ' + CAST(INSERTED.chef_id as varchar(3)) + 
		', lonakat_id: ' + CAST(INSERTED.lonekat_id as varchar(3)) + 
		', fornamn: ' + LTRIM(RTRIM(INSERTED.fornamn)) +
		', efternamn: ' + LTRIM(RTRIM(INSERTED.efternamn)) + 
		', titel: ' + LTRIM(RTRIM(INSERTED.titel))
	)
FROM INSERTED
full outer join DELETED
on INSERTED.personal_id = DELETED.personal_id
END

Hello Visakh!
Wouldn't that give several hist (in the logging table) when I am using an update?

Best Regards,
KF

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-26 : 08:49:24

nope. how? for each record you update, you will have only 1 corresponding record in both INSERTED & DELETED. not sure what you meant by several hist? can you elaborate?

kfluffie
Posting Yak Master

103 Posts

Posted - 2009-01-26 : 09:39:35

quote:
Originally posted by visakh16

nope. how? for each record you update, you will have only 1 corresponding record in both INSERTED & DELETED. not sure what you meant by several hist? can you elaborate?

You are correct!
Thanks!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-26 : 09:41:05

welcome

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources