unique exception make some breaks in sql work

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)

unique exception make some breaks in sql work

Author

Topic

marconi8
Yak Posting Veteran

73 Posts

Posted - 2003-05-15 : 15:03:00

==============================================
short description
==============================================
----------------------------------------------
i have two tables table_1,table_2
table_1 has a trigger_1 and any unique constraints (only a trigger)
table_2 has a unique constraints
----------------------------------------------
==============================================

==============================================
my task
==============================================
----------------------------------------------
when i insert data to the table_1, with trigger help
i must to insert data also into table_2, table_1 data are inserted into table_2 by using table_1.trigger_1, all is good, this mean that
when i make an insert into table_1 then data inserts into table_2 with trigger.

into table_1 i can write duplicate entrys, but into table_2 i can't, because on table_2 is set unique constraint.
----------------------------------------------
==============================================

==============================================
problem description
==============================================
----------------------------------------------
when i am trying to insert data into table_1 which already presents
in table_2 then i cannot see inserted data in table_1, table_1 must accept duplicate entries but table_2 can't, whats wrong.
----------------------------------------------
==============================================

here is table_1.trigger_1 (in short form)


TRIGGER trigger_1
     ON table_1
    FOR INSERT
AS
BEGIN
  INSERT table_2
  SELECT (some calculated value by this trigger),
         F1,
         F2,
         F3...........and other fields
  FROM INSERTED   
END

thanks.....

i know that this basic problem i can solve with NOT EXISTS check help,
but this will be not good choice, one select is better than one select + check with select

Edited by - marconi8 on 05/15/2003 15:05:08

Edited by - marconi8 on 05/15/2003 15:12:05

pmr
Starting Member

37 Posts

Posted - 2003-05-15 : 16:01:15

Assuming F1 is your primary key:


CREATE TRIGGER trigger_1
     ON table_1
    FOR INSERT
AS
BEGIN
  INSERT table_2
  SELECT (some calculated value by this trigger),
         INSERTED.F1,
         INSERTED.F2,
         INSERTED.F3...........and other fields
  FROM INSERTED
LEFT OUTER JOIN table_2 ON INSERTED.F1 = table_2.F1
WHERE table_2.F1 IS NULL

END

Peter

Lavos
Posting Yak Master

200 Posts

Posted - 2003-05-15 : 22:36:05

And another Option, though not highly suggested :)


CREATE TABLE #Temp
 ( a int )
GO
CREATE UNIQUE INDEX #Kludge ON #Temp (a) WITH IGNORE_DUP_KEY
GO

INSERT INTO #Temp(a)
VALUES(1)
INSERT INTO #Temp(a)
VALUES(2)
INSERT INTO #Temp(a)
VALUES(1)
INSERT INTO #Temp(a)
VALUES(4)

GO

SELECT * FROM #Temp

GO
DROP TABLE #Temp



Mark-up SQLTeam posts here 
http://www.markitup.com/Forum/SQLTeam.asp

----------------------
"O Theos mou! Echo ten labrida en te mou kephale!"

So Long and Thanks for the Links.

Edited by - Lavos on 05/15/2003 22:36:53

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources