AFTER Trigger using AD HOC relationship

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)

AFTER Trigger using AD HOC relationship

Author

Topic

LilySQL
Starting Member

1 Post

Posted - 2006-05-03 : 14:28:20

This trigger has problem. I don't know how to use AD HOC relationship in JOIN. Tks for any advice.
--------------------------------------------------------------------------------------------------
Wrrite a trigger that prohibits duplicate value. If an INSERT or UPDATE statement creates a duplicate value in NoDupName, roll back the statement and return an error message. (Hint: Use an ad hoc relationship between TestUniqueNulls and Inserted tables.) The structure of the TestUniqueNulls table is as follows:

CREATE TABLE TestUniqueNulls
(RowID INT IDENTITY NOT NULL, NoDupName VARCHAR(20) NULL)

Use an INSERT statement to test the trigger with one result showing ¡§one row affected¡¨ and one showing the error message.
----------------------------------------------------------------------------------------------------

USE AP
GO
IF OBJECT_ID('TestUniqueNulls') IS NOT NULL
DROP TABLE TestUniqueNulls
CREATE TABLE TestUniqueNulls
(RowID INT IDENTITY NOT NULL, NoDupName VARCHAR(20) NULL)
GO

CREATE TRIGGER trNoDupAllow_INSERT_UPDATE
ON TestUniqueNulls
AFTER INSERT,UPDATE
AS
IF EXISTS (SELECT COUNT(*) AS CountNoDupName
FROM TestUniqueNulls JOIN INSERTED
ON TestUniqueNulls.NoDupName = INSERTED.NoDupName
HAVING CountNoDupName <>1 )
BEGIN
RAISERROR('DuplicateValue won''t be accepted!',1,1)
ROLLBACK TRAN
END
GO

--------Test trigger -------
INSERT INTO TestUniqueNulls
VALUES ('UM8000-5404')

INSERT INTO TestUniqueNulls
VALUES ('UM8000-5404')

Lily am 11:30

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2006-05-03 : 14:51:39

Just tell your teacher you decided to use a unique constraint instead of a trigger.

EDIT:
what's an ad hoc relationship? Should I know that?

Be One with the Optimizer
TG

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-05-03 : 14:53:44

What is an ad hoc relationship?

Tara Kizer
aka tduggan

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-05-03 : 16:25:58

quote:
Originally posted by tkizer

What is an ad hoc relationship?

Tara Kizer
aka tduggan

http://www.teachtime.com/en/wikipedia/d/da/database.html
...Relational databases also consist of multiple database tables. Unlike the hierarchical and network models, there are no explicit pointers; in theory, columns of any type may be used to create an ad-hoc relationship between two or more tables...

CODO ERGO SUM

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2006-05-03 : 20:56:10

oh like a join then

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

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-05-03 : 21:11:55

"AD HOC relationship in JOIN"
Conditional Join of the table ?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources