Trigger which make rollback... - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

Script Library

Trigger which make rollback...

Author

Topic

karrisql.com
Starting Member

3 Posts

Posted - 2007-01-15 : 07:55:51

Hi,

I need help for this:
I need to make trigger which makes rollback
if INSERT- or UPDATE-statement try to make
dublicate row (col1+col2)
I try this, but it don't work:

CREATE TRIGGER trg_col1_col2_check
ON TABLE1
AFTER INSERT,UPDATE
AS
BEGIN
SET NOCOUNT ON
IF (SELECT DISTINCT 1
FROM TABLE1
GROUP BY col1, col2
HAVING count(*) > 1) = 1
BEGIN
IF (SELECT
case
when (i.col1 = ''
AND i.col2 = '') then 0
else 1
end
FROM INSERTED i) = 1
BEGIN
Print 'Col1 and col2 Already exist'
ROLLBACK
END
END
END

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-01-15 : 08:12:15

How about this?

Create Trigger ...
...

begin
If Exists(Select t.col1, t.col2 from table1 t join inserted i on t.pk = i.pk group by t.col1, t.col2 having count(*)>1)
begin
	Print 'Col1 and col2 Already exist'
	ROLLBACK
end
end

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2007-01-15 : 09:50:30

Why don't you just create a unique constraint on the table on the table to do this?

CODO ERGO SUM

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-01-15 : 11:13:02

having that kind of logic is IMO always a bad thing.

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

blog thingie: http://weblogs.sqlteam.com/mladenp

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2007-01-15 : 12:02:42

Which..."having that kind of logic is IMO always a bad thing."....MVJ's or Harsh's?

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-01-15 : 12:08:28

i meant the original posters request.

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

blog thingie: http://weblogs.sqlteam.com/mladenp

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2007-01-15 : 16:33:55

> "Why don't you just create a unique constraint on the table on the table to do this?"

> "having that kind of logic is IMO always a bad thing."

quote:
Originally posted by AndrewMurphy

Which..."having that kind of logic is IMO always a bad thing."....MVJ's or Harsh's?

That made me laugh Andrew

Congrats on finally breaking 2000!

Cheers and Health

__________________
still chuckling

rockmoose

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2007-01-15 : 19:11:44

quote:
Originally posted by AndrewMurphy

Which..."having that kind of logic is IMO always a bad thing."....MVJ's or Harsh's?

I get no respect.

CODO ERGO SUM

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2007-01-16 : 07:34:41

I get no respect. Do I hear a violin playing....

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources