Creating Trigger - (Resolved) - 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 2005 Forums

Transact-SQL (2005)

Creating Trigger - (Resolved)

Author

Topic

shashwat2691
Starting Member

11 Posts

Posted - 2010-04-19 : 10:55:14

Hello dear

I want some kinds of validation on my table

Suppose my Table is just like show below
-------------------------
A |B |C |
-------------------------
1 |1 |2 |
2 |2 |1 |

here is a record in my table containing values 1,1,2.
I want that user should never allowed to insert same values again in this Table. Means 1,1,2 and 2,2,1 should never be inserted again in this table.

I have implemented so many kinds of validation from the front end side in my Application to avoid this. But I found that here should also a restriction from the Back End (Database).

I know that the brilliants will solve my problem soon.
that's why thank a lot in ADVANCE.....

Shashwat Tripathi - INDIA

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-04-19 : 10:56:55

PRIMARY KEY on all columns (or a unique constraint on all the columns)

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-04-19 : 11:01:10

Example


IF OBJECT_ID('tempDb..#foo') IS NOT NULL DROP TABLE #foo

CREATE TABLE #foo (
	[a] INT
	, [b] INT
	, [c] INT

	CONSTRAINT PK_FOO_A_B_C PRIMARY KEY([a], [b], [c])
	)

-- Insert into #foo
INSERT #foo ([a],[b],[c])
SELECT 1, 2, 3

SELECT * FROM #foo

-- Insert into #foo (2)
INSERT #foo ([a],[b],[c])
SELECT 1, 2, 3

SELECT * FROM #foo

IF OBJECT_ID('tempDb..#bar') IS NOT NULL DROP TABLE bar

CREATE TABLE #bar (
	[a] INT
	, [b] INT
	, [c] INT

	CONSTRAINT UK_BAR_A_B_C UNIQUE ([a], [b], [c])
	)

-- Insert into ##bar
INSERT #bar ([a],[b],[c])
SELECT 1, 2, 3

SELECT * FROM #bar

-- Insert into ##bar (2)
INSERT #bar ([a],[b],[c])
SELECT 1, 2, 3

SELECT * FROM #bar

Gives the following test on execution



(1 row(s) affected)

(1 row(s) affected)
Msg 2627, Level 14, State 1, Line 18
Violation of PRIMARY KEY constraint 'PK_FOO_A_B_C'. Cannot insert duplicate key in object 'dbo.#foo'.
The statement has been terminated.

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)
Msg 2627, Level 14, State 1, Line 40
Violation of UNIQUE KEY constraint 'UK_BAR_A_B_C'. Cannot insert duplicate key in object 'dbo.#bar'.
The statement has been terminated.

(1 row(s) affected)

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

shashwat2691
Starting Member

11 Posts

Posted - 2010-04-19 : 11:05:36

If I will apply Primary Key or Unique on All columns

then How will i insert

2,2,2

I want that the only same sets of value should not inserted...

and whats mean by '#'

Shashwat Tripathi - INDIA

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-19 : 11:08:43

quote:
Originally posted by shashwat2691

If I will apply Primary Key or Unique on All columns

then How will i insert

2,2,2

I want that the only same sets of value should not inserted...

Shashwat Tripathi - INDIA

if you apply unique or primary key on composite group col1,col2,col3 then it only ensures the group values of (col1,col2,col3) is unique it doesnt look for uniqueness of each of the values individually and not among them . so you can still insert 2,2,2 one time.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-04-19 : 11:09:05

if the primary key or unique constraint is over all columns then 2, 2, 2 is perfectly valid

Example



IF OBJECT_ID('tempDb..#bar') IS NOT NULL DROP TABLE #bar

CREATE TABLE #bar (
	[a] INT
	, [b] INT
	, [c] INT

	CONSTRAINT UK_BAR_A_B_C UNIQUE ([a], [b], [c])
	)

-- Insert into #bar
INSERT #bar ([a],[b],[c])
SELECT 2, 2, 2

SELECT * FROM #bar

Will work fine (but only if there is no value of 2,2,2 in the table before insert.
If you declare a key or constraint and provide a column list then that key is called a compound key and it applies the restriction to the set of columns. Not each column individually.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-04-19 : 11:12:25

quote:
Originally posted by shashwat2691

If I will apply Primary Key or Unique on All columns

then How will i insert

2,2,2

I want that the only same sets of value should not inserted...

and whats mean by '#'

Shashwat Tripathi - INDIA

a hash table is called a temporary table -- they only exist for the scope of the running code. So if you have a query window open the table automatically gets dropped when the window closes. The tables are created in the tempdb database.

They are often used inside stored procedures to store data used by the procedure.

The code I posted is safe to run in your environment for testing because it won't create any permanent objects.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-19 : 11:12:33

quote:
Originally posted by shashwat2691

If I will apply Primary Key or Unique on All columns

then How will i insert

2,2,2

I want that the only same sets of value should not inserted...

and whats mean by '#'

Shashwat Tripathi - INDIA

# denotes its a temporary table.

see more details here

http://databases.aspfaq.com/database/should-i-use-a-temp-table-or-a-table-variable.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

shashwat2691
Starting Member

11 Posts

Posted - 2010-04-19 : 11:20:51

Thanks a lot

but please tell how can i alter my actual table with this validation

My Table code is:

USE [ACN]
GO
/****** Object: Table [dbo].[Incoming] Script Date: 04/19/2010 20:46:46 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Incoming](
[E_Date] [datetime] NOT NULL,
[A_Date] [datetime] NOT NULL,
[ACN] [nvarchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[Day] [nvarchar](5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Par] [nvarchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[Amount] [bigint] NOT NULL,
[As] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[Marker] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]

I want this kind of validation on [A_Date], [ACN], [As] columns

I can do it but not by command. I want to know the code to do this..

Shashwat Tripathi - INDIA

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-19 : 11:26:00

just look for syntax of ALTER TABLE...ADD CONSTRAINT in books online

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-04-19 : 11:27:11

UNique Constraint



ALTER TABLE dbo.Incoming ADD CONSTRAINT UNK_Incoming_A_Date_ACN_As  UNIQUE ([A_DATE], [ACN], [AS])

Your table currently has no primary key. You should think about your data and come up with a good primary key candidate.

You *could* implement the unique constraint as a primary key but that's probably not very logical. A key is supposed to uniquely identify a row in the table.

It's generally best that the key makes the most sense to your business / data model. Do you want the fact that the data must be different for those three columns to be the defining feature of the table? Or is there a better condition that helps you uniquely identify a row?

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-04-19 : 11:30:15

You might want to read up on some database design tutorials. Here's a reasonable introduction to key concepts. There are lot's of reasons why you'd want a strongly related database and without keys you can't do that well.

http://immike.net/blog/2007/08/14/database-design-choosing-a-primary-key/

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

shashwat2691
Starting Member

11 Posts

Posted - 2010-04-19 : 11:32:50

Thanks a Lot to all repliers ...

I am new on sqlteam.com.

Is here any option to set this issue resolved..

Shashwat Tripathi - INDIA

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-19 : 11:34:40

quote:
Originally posted by shashwat2691

Thanks a Lot to all repliers ...

I am new on sqlteam.com.

Is here any option to set this issue resolved..

Shashwat Tripathi - INDIA

Nothing as such
You may append Resolved to thread header if you want

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources