type of trigger to use - 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 2000 Forums

Transact-SQL (2000)

type of trigger to use

Author

Topic

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2005-01-04 : 05:27:25

not sure if this is a simple problem but i cant' seem to solve it.

i have table audit with a trigger for update and delete
the trigger checks if the host is authorized to do this, so,
1. i capture the data being modified (old one), save it onto table1
2. then will not commit the changes on the audit.

problem is, everytime i do a rollback, step 1 gets rolled back too.
i tried the instead of trigger, but the trigger is not updating the table

---------------------------------------------------------------
alter trigger trgAudit on Audit
instead of delete,update
as

if exists (select hostname from master..sysprocesses where spid=@@spid
and hostname not in (select computername from CompNames))
--insert into table1 some data --successful
else
update audit
set field1=(select field1 from deleted)
where [id]=(select [id] from deleted)

--------------------------------------------------------------

alter trigger trgAudit on Audit
for delete,update
as

if exists (select hostname from master..sysprocesses where spid=@@spid
and hostname not in (select computername from CompNames))
begin
begin tran T1
--insert into table1 some data --successful
commit tran T1
rollback
end

any idea appreciated...

--------------------
keeping it simple...

nr
SQLTeam MVY

12543 Posts

Posted - 2005-01-04 : 05:48:40

The trigger runs inside the transaction of the firing statement. If you roll that back then you roll back whatever the trigger does.
There are a few ways round this biut none very nice.

Write the data out to a file then import it using a new connection (e.g. bcp).
Write the data to a table then start a job (new connection) which copies it to the table using a nolock hint.

==========================================
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.

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2005-01-04 : 06:00:07

thanks nigel for the fast reply

if i write the data to a file then issue a rollback inside the trigger, does sql have the capability of "erasing" the file or that will be left alone?

--------------------
keeping it simple...

nr
SQLTeam MVY

12543 Posts

Posted - 2005-01-04 : 06:27:38

Nope - the file will remain.

==========================================
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.

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2005-01-04 : 06:33:20

jen;

also, I'd suggest to select deleted row into a set of known data types (and number)

variables and then, after rolling back, insert these variables into Audit table.

select @v1, @v2, ... from deleted

rollback tran

insert into Audit select @v1, @v2, ...

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2005-01-04 : 06:35:53

select @v1=col1, @v2=col2, ... from deleted

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2005-01-04 : 06:35:59

i tried that stoad, that was my first problem with the rollback, it rolls back even the values set to variables so i get null values in the fields

i'll try nigel's suggestion, but feel free to post more ideas guys, your help is truly appreciated

--------------------
keeping it simple...

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2005-01-04 : 06:43:53

i believe i might have a problem with bcp, my user is not SA and have no permission to execute xp_cmdshell within

is there some kind of file handling (read and write) using tsql for the "common" user?

--------------------
keeping it simple...

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2005-01-04 : 06:51:27

hm.. not sure what you mean jen..

-- drop table t drop table tt
create table t(m int)
create table tt(m int)
GO

create trigger jen on t for delete
as
declare @m int
select @m=m from deleted
rollback tran
insert into tt select @m*22
GO

insert into t select 555
delete from t
select * from t
select * from tt

m
-----------
555

m
-----------
12210

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2005-01-04 : 07:12:58

hmm... i must be doing something really wrong or assumed the trigger was not working coz it was saving null values in the audit table. the old script is actually working having tested it on the devserver now.

must have messed up somewhere. sorry, i'm not getting enough sleep again.

thanks very much stoad... i'm happy to know the trigger was working all along (just need to investigate the null values) but users won't (since their actions are audited)

--------------------
keeping it simple...

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2005-01-04 : 08:06:16

aha jen! those users (and bosses) are our worst enemies.

Kristen
Test

22859 Posts

Posted - 2005-01-06 : 00:03:28

Jen, I didn't read the thread carefully, but could you use a save point?


BEGIN TRANSACTION MyName_01
SAVE  TRANSACTION MyName_02

... do work here ...

IF @MyErrorCode = 0
BEGIN
	COMMIT TRANSACTION MyName_01
END
ELSE
BEGIN
	ROLLBACK TRANSACTION MyName_02
	COMMIT TRANSACTION MyName_01
END

so that you always end up with a commit, rather than a rollback

Kristen

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2005-01-06 : 03:28:30

quote:
Originally posted by Kristen

Jen, I didn't read the thread carefully, but could you use a save point?

BEGIN TRANSACTION MyName_01
SAVE TRANSACTION MyName_02

... do work here ...

IF @MyErrorCode = 0
BEGIN
COMMIT TRANSACTION MyName_01
END
ELSE
BEGIN
ROLLBACK TRANSACTION MyName_02
COMMIT TRANSACTION MyName_01
END

so that you always end up with a commit, rather than a rollback

Kristen

tnx kristen, i'll try this

but i just figured out the problem, i forgot to "relocate" one transaction right after the rollback.

that's why i'm getting null values, coz audit data was rolled back too, but weird coz the records were preserved but only the identity column filled up.

i thought rollback will roll back the transactions including inserts?

--------------------
keeping it simple...

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2005-01-06 : 04:03:26

jen;

btw, if really "keeping it simple", you need no "rolling back" at all.

You can just insert the deleted row(s) back (along with inserting it into Audit table).

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2005-01-06 : 05:19:16

not an option, the trigger should prevent any malicious changes made by users who have a copy of the source code and try to manipulate the data "unknowingly". The culprits have been dealt with but just to avoid such incident from happening again.

unlucky for them, in sql there are very few transactions that are unknown.

--------------------
keeping it simple...

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2005-01-06 : 05:43:35

of course, I didn't mean that you should restore those

deleted rows "manually"; I meant it for the trigger.

In some sense, "rollback tran" == "insert deleted rows back".

Kristen
Test

22859 Posts

Posted - 2005-01-06 : 18:37:13

"that's why i'm getting null values, coz audit data was rolled back too, but weird coz the records were preserved but only the identity column filled up.

i thought rollback will roll back the transactions including inserts?"

Not sure I've quite understood - IDENTITY values used during an INSERT are "lost" when you rollback. The next INSERT will, in effect, leave a gap in the IDENTITY range used [but I'm sure you know that, sorry]

Or do you mean that the INSERTS into the table are NOT rollback'd? That would be scary!

Kristen

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2005-01-06 : 21:58:50

quote:
Originally posted by Stoad

of course, I didn't mean that you should restore those

deleted rows "manually"; I meant it for the trigger.

In some sense, "rollback tran" == "insert deleted rows back".

oh, i've thought of that, but i was too afraid to try it coz, if you do that then it triggers again for update (nested trigger (?)) insert is also not an option since the identity column is also being used for tracking (coz they're doing inserts too!)

not sure about it, but if you do make an insert via the trigger, won't it "trigger" the trigger again?

--------------------
keeping it simple...

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2005-01-06 : 22:02:27

quote:
Originally posted by Kristen

"that's why i'm getting null values, coz audit data was rolled back too, but weird coz the records were preserved but only the identity column filled up.

i thought rollback will roll back the transactions including inserts?"

Not sure I've quite understood - IDENTITY values used during an INSERT are "lost" when you rollback. The next INSERT will, in effect, leave a gap in the IDENTITY range used [but I'm sure you know that, sorry]

Or do you mean that the INSERTS into the table are NOT rollback'd? That would be scary!

Kristen

Not to scare you Kristen, but that's what's happening. Ii would have expected the gaps but then, the identity column has a value but the other fields null.

I'm not sure if this is an isolated case or someone has also encountered this before.

--------------------
keeping it simple...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources