CREATE TRIGGER NEW TO SQL - 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)

CREATE TRIGGER NEW TO SQL

Author

Topic

abcd
Yak Posting Veteran

92 Posts

Posted - 2008-12-31 : 05:59:55

I have a table having two columns name and serial number.
i want to create a trigger as whenever any serial number is entered to modify the table, if the serial number already exist trigger raises an exception.

raky
Aged Yak Warrior

767 Posts

Posted - 2008-12-31 : 06:10:11

I think there is no need for trigger just create Unique constraint on that serialnumber column and this will not allow to have duplicate values

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2008-12-31 : 06:17:11

just create a sp

CREATE PROC [dbo].usp_sample
(
@errorId INT = NULL OUTPUT,
@errorMessage VARCHAR(2048) = NULL OUTPUT,
@return INT = NULL OUTPUT,
@name VARCHAR(32),
@serialnumber INT
)
AS
SET NOCOUNT ON
BEGIN

DECLARE @cnt INT
SELECT
@cnt = COUNT(1)
FROM urtable WHERE serialnumber = @serialnumber

INSERT INTO urtable
( serialnumber,name )
SELECT
@serialnumber, @name
WHERE
@cnt = 0

SELECT @return = 0,
@errorid = 0,
@errormessage = 'Record Inserted Successfully' WHERE @cnt = 0

SELECT @return = 1,
@errorid = 0,
@errormessage = 'Record Already Exists With This Combination' WHERE @cnt <> 0
END
SET NOCOUNT OFF

and u can handle it in front end by error message
i think so just once check it

abcd
Yak Posting Veteran

92 Posts

Posted - 2008-12-31 : 06:17:42

no i am just asking for practise of triggers...
its urgent.....
do help ......

abcd
Yak Posting Veteran

92 Posts

Posted - 2008-12-31 : 06:20:05

thnks bklr,,,,
but i want to know if code can be a little smaller,,,
or some other way out.,,,

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-31 : 06:35:36

quote:
Originally posted by abcd

no i am just asking for practise of triggers...
its urgent.....
do help ......

but its not a good approach to create trigger for this. what you need is just a unique constraint. Using trigger will hurt the performance.

abcd
Yak Posting Veteran

92 Posts

Posted - 2008-12-31 : 06:37:00

THIS QUERY IN NOT INCLUDED IN ANY DATABASE WORKING...
I AM JUST PRACTISING THE TRIGGERS...
SO DO HELP...
PLEASE GIVE SOME CODE..

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-31 : 06:46:33

quote:
Originally posted by abcd

THIS QUERY IN NOT INCLUDED IN ANY DATABASE WORKING...
I AM JUST PRACTISING THE TRIGGERS...
SO DO HELP...
PLEASE GIVE SOME CODE..


CREATE TRIGGER YourTrigger
ON YourTable
INSTEAD OF UPDATE
AS
BEGIN
IF EXISTS (SELECT 1 FROM YourTable t
           INNER JOIN INSERTED i
           ON i.serial_number=t.serial_number)
RAISERROR 'Duplicate serialnumber',10,1
ELSE
INSERT INTO YourTable
SELECT columns FROM INSERTED
END

abcd
Yak Posting Veteran

92 Posts

Posted - 2008-12-31 : 06:58:07

I HAVE TO USE INSERT TRIGGER
AO I MADE SOME CHANGES
BUT EROR IS COMING.

CREATE TRIGGER tr_repdata
AFTER INSERT ON PRACTISE
FOR EACH ROW
AS
BEGIN
IF EXISTS (SELECT 1 FROM PRACTISE AS P
INNER JOIN INSERTED i
ON i.serial_number=P.serial_number)
RAISERROR 'Duplicate serialnumber',10,1
ELSE
INSERT INTO PRACTISE
SELECT SERIALNO FROM INSERTED
END

ERROR

Msg 102, Level 15, State 1, Procedure tr_repdata, Line 2
Incorrect syntax near 'AFTER'.
Msg 102, Level 15, State 1, Procedure tr_repdata, Line 9
Incorrect syntax near 'Duplicate serialnumber'.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-31 : 07:04:08

quote:
Originally posted by abcd

I HAVE TO USE INSERT TRIGGER
AO I MADE SOME CHANGES
BUT EROR IS COMING.

CREATE TRIGGER tr_repdata
ON PRACTISE
~~AFTER~~INSTEAD OF INSERT
~~FOR EACH ROW~~
AS
BEGIN
IF EXISTS (SELECT 1 FROM PRACTISE AS P
INNER JOIN INSERTED i
ON i.serial_number=P.serial_number)
RAISERROR 'Duplicate serialnumber',10,1
ELSE
INSERT INTO PRACTISE
SELECT SERIALNO FROM INSERTED
END

ERROR

Msg 102, Level 15, State 1, Procedure tr_repdata, Line 2
Incorrect syntax near 'AFTER'.
Msg 102, Level 15, State 1, Procedure tr_repdata, Line 9
Incorrect syntax near 'Duplicate serialnumber'.

you dont need for each row. also it should be instead of rather than after

abcd
Yak Posting Veteran

92 Posts

Posted - 2008-12-31 : 07:11:01

I DID AS U SUGGESTED BUT NOW ERROR IS COMING LIKE THIS...

Msg 102, Level 15, State 1, Procedure tr_repdata, Line 9
Incorrect syntax near 'Duplicate serialnumber'.

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2008-12-31 : 07:16:42

CREATE TRIGGER tr_repdata
AFTER INSERT ON PRACTISE
FOR EACH ROW
AS
BEGIN
IF EXISTS (SELECT 1 FROM PRACTISE AS P
INNER JOIN INSERTED i
ON i.serial_number=P.serial_number)
RAISERROR ('Duplicate serialnumber',10,1)
ELSE
INSERT INTO PRACTISE
SELECT SERIALNO FROM INSERTED
END

u forgot to keep braces

abcd
Yak Posting Veteran

92 Posts

Posted - 2008-12-31 : 23:50:36

thnks a lot for the suggestions......
looking forward to more help...

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-01-01 : 00:23:46

you're welcome

wish u happy new year to all

abcd
Yak Posting Veteran

92 Posts

Posted - 2009-01-01 : 00:42:05

HAPPY NEW YEAR TO YOU TOO,,,

M HAVING A NEW PROBLEM AGAIN WITH THE ABOVE QUERY...
WHEN I AM INSERTING THE SAME SERIALNO. MESSAGE IS COMING DUPLICATE NUMBER
BUT IF A I AM INSERTING ANY NEW SERIAL NUMBER THEN NO UPDATION IS OCCURING AND THE MESSAGE DISPLAYED IS TRANSACTION ENDED IN THE TRIGGER
THE BATCH HAS BEEN ABORTED.

DO SUGGEST SOMETHING...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources