Vaidation Trigger - 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

SQL Server Development (2000)

Vaidation Trigger

Author

Topic

JimL
SQL Slinging Yak Ranger

1537 Posts

Posted - 2005-09-19 : 15:49:03

Is it possible to right an update trigger to cause an update to fail if it meets certain criteria?
Example I have a CM table that is currently under development and initial population. Because of multiple imports and ways to get data into it we are having duplicates with minor variations that need to be removed. To do this I have the user change the company name to delete and it gets dropped during the nightly maintenance. But I cannot drop the entry if it was initiated through the web so I do not want to let them update to delete if it came from the web.

CREATE TRIGGER MasterSLSContact_update
ON dbo.MasterSLSContact
FOR UPDATE
AS
Declare @SLSContact int

Select @SLSContact = SLSContact
From inserted

If comp = 'delete' and RecNum is > 1
from dbo.MasterSLSContact
where SLSContact = @SLSContact
begin

Cause update to fail
end

Jim
Users <> Logic

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-09-19 : 15:54:38

Cause update to fail = ROLLBACK TRAN

Tara

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-09-19 : 16:54:44

rollback tran inside a trigger??
doesn't that screw up commit's and/or rollback's down the line?

Go with the flow & have fun! Else fight the flow

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-09-19 : 16:57:06

Yes inside a trigger. It'll rollback the current transaction when it meets the IF condition.

Tara

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-09-19 : 16:59:03

i understand that. but what if:
begin tran
insert/update stuff... -> trigger that does rollback if the if is met which let's say it is so the tran is rolledback
some other stuff...
commit <- doesn't this throw an error that it doesn't have a transaction to commit?

Go with the flow & have fun! Else fight the flow

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-09-19 : 17:02:22

Well you have to be aware what you are doing inside the trigger and make appropriate code changes elsewhere.

Tara

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-09-19 : 17:03:21