Problem with a 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

General SQL Server Forums

New to SQL Server Programming

Problem with a trigger

Author

Topic

alact
Starting Member

3 Posts

Posted - 2006-06-16 : 03:29:48

hello,
i'm writing a trigger which will prevent a row with same data to be inserted into the table. but i'm getting the problem that neither row can be inserted whether it is in or not in the table

i've asked a person about this, and he said that it is because before this trigger do the check the row from Insert is inserted to table therefore i will always get the error message.

so my question is what i should add so it will prevent the row from Inserted to be added into the table.

here's my trigger btw.
-----------------------------------------------------
CREATE TRIGGER ej_dubbel_bokad_lokal

ON BokningsSchema
FOR INSERT AS

DECLARE @LokalKod CHAR(2)
DECLARE @Vecka TINYINT
DECLARE @Dag VARCHAR(2)
DECLARE @Tid VARCHAR(5)

DECLARE @Svar_LokalKod CHAR(2)
DECLARE @Svar_Vecka TINYINT
DECLARE @Svar_Dag VARCHAR(2)
DECLARE @Svar_Tid VARCHAR(5)

SELECT @LokalKod = LokalKod, @Vecka = Vecka, @Dag = Dag, @Tid = Tid
FROM inserted

SELECT @Svar_LokalKod = LokalKod, @Svar_Vecka = Vecka, @Svar_Dag = Dag, @Svar_Tid = Tid
FROM bokningsschema
WHERE lokalkod = @lokalkod AND vecka = @vecka AND dag = @dag AND tid = @tid

IF @svar_lokalkod = @lokalkod AND @svar_vecka = @vecka AND @svar_dag = @dag AND @svar_tid = @tid
BEGIN
RAISERROR ('Upptagen', 16,1 )
ROLLBACK TRANSACTION
END

CSK
Constraint Violating Yak Guru

489 Posts

Posted - 2006-06-16 : 03:54:10

use INSTEAD OF INSERT

Thanks
KK

psawant
Starting Member

14 Posts

Posted - 2006-06-16 : 04:07:02

I think you missed the else part in your trigger if the record is present then RAISERROR else insert from inserted.

Try this it should work.

Prakash Sawant
http://psawant.blogspot.com

CSK
Constraint Violating Yak Guru

489 Posts

Posted - 2006-06-16 : 04:52:51

other wise try like this

Create TABLE BaseTable
(PrimaryKey int ,
Color nvarchar(10) NOT NULL,
Material nvarchar(10) NOT NULL,
ComputedCol AS (Color + Material)
)
GO

alter TRIGGER Tr_test on Basetable
For INSERT
AS
BEGIN
if ( Select Count(*) from inserted i, basetable b
where i.PrimaryKey = b. PrimaryKey
and i.Color = b. Color
and i.Material = b. Material
)>1

Begin
Print 'Already Exists'
Rollback Tran
Return
End
Else
Print 'Inserted'

END
GO

insert into BaseTable Select 1,'BLUE','Leather1'
insert into BaseTable Select 1,'BLUE','Leather1'
insert into BaseTable Select 1,'BLUE','Leather2'
insert into BaseTable Select 1,'BLUE','Leather2'

alact
Starting Member

3 Posts

Posted - 2006-06-16 : 05:10:17

hello, thanks for the help
just a question about your idea psawant. i'm going to try your solution to CSK

should it look like this if i write it with THEN?
--------------------------------------------------
CREATE TRIGGER ej_dubbel_bokad_lokal

ON BokningsSchema
FOR INSERT AS

DECLARE @LokalKod CHAR(2)
DECLARE @Vecka TINYINT
DECLARE @Dag VARCHAR(2)
DECLARE @Tid VARCHAR(5)

DECLARE @Svar_LokalKod CHAR(2)
DECLARE @Svar_Vecka TINYINT
DECLARE @Svar_Dag VARCHAR(2)
DECLARE @Svar_Tid VARCHAR(5)

SELECT @LokalKod = LokalKod, @Vecka = Vecka, @Dag = Dag, @Tid = Tid
FROM inserted

IF (SELECT @Svar_LokalKod = LokalKod, @Svar_Vecka = Vecka, @Svar_Dag = Dag, @Svar_Tid = Tid
FROM bokningsschema WHERE lokalkod = @lokalkod AND vecka = @vecka AND dag = @dag AND tid = @tid)
THEN RAISERROR ('Upptagen', 16,1 )
ELSE INSERT FROM INSERTED

psawant
Starting Member

14 Posts

Posted - 2006-06-16 : 05:22:18

REPLACE
[CODE]
INSERT FROM INSERTED
[/CODE]

WITH
[CODE]

INSERT INTO bokningsschema VALUES (@LokalKod,@Vecka,@Dag,@Tid)
[/CODE]

------------------------
Prakash Sawant
http://psawant.blogspot.com

alact
Starting Member

3 Posts

Posted - 2006-06-17 : 04:49:44

hey it works now, thanks for the help you two :)

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-06-17 : 13:07:01

quote:
Originally posted by alact

hello, thanks for the help
just a question about your idea psawant. i'm going to try your solution to CSK

should it look like this if i write it with THEN?
--------------------------------------------------
CREATE TRIGGER ej_dubbel_bokad_lokal

ON BokningsSchema
FOR INSERT AS

DECLARE @LokalKod CHAR(2)
DECLARE @Vecka TINYINT
DECLARE @Dag VARCHAR(2)
DECLARE @Tid VARCHAR(5)

DECLARE @Svar_LokalKod CHAR(2)
DECLARE @Svar_Vecka TINYINT
DECLARE @Svar_Dag VARCHAR(2)
DECLARE @Svar_Tid VARCHAR(5)

SELECT @LokalKod = LokalKod, @Vecka = Vecka, @Dag = Dag, @Tid = Tid
FROM inserted

IF (SELECT @Svar_LokalKod = LokalKod, @Svar_Vecka = Vecka, @Svar_Dag = Dag, @Svar_Tid = Tid
FROM bokningsschema WHERE lokalkod = @lokalkod AND vecka = @vecka AND dag = @dag AND tid = @tid)
THEN RAISERROR ('Upptagen', 16,1 )
ELSE INSERT FROM INSERTED

It is common to make the error when developing a trigger to not allow for the possibility that more then one row may be inserted, updated, or deleted.

You need to re-program the trigger to allow for multiple rows being inserted.

CODO ERGO SUM

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources