TechTalkz.com Logo Ask the Experts!

Go Back   TechTalkz.com Technology & Computer Troubleshooting Forums > Tech World > General Q&A

SQL Trigger

General Q&A

 
Reply
Thread Tools Display Modes
Unread 13-08-2014, 11:53 AM   #1
Newbie
Thread Starter
 
Join Date: Aug 2014
Age: 33
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Rep Power: 0 JR83 is an unknown quantity at this point

Intel

Windows 7


Unhappy SQL Trigger

HI There

I have a trigger that checks for duplications already in the table and if so doesnt insert otherwise inserts a new value

Here is the trigger:

ALTER TRIGGER [dbo].[DUPLICATES] ON [dbo].[AMGR_User_Fields_Tbl]
-- When inserting or updating
AFTER INSERT, UPDATE AS

-- Declare the variables
DECLARE @AN varchar(200)



-- Set the variable to the value that has been inserted.
SELECT @AN = AlphanumericCol FROM inserted

-- If the AlphanumericCol is not null
IF @AN IS NOT NULL
BEGIN

-- If a duplicate has been found, rollback trasaction and report error.
if exists ( select * from AMGR_User_Fields_Tbl t
inner join inserted i on i.AlphanumericCol=t.AlphanumericCol)
BEGIN
RAISERROR ('Serial Number: %s already exists in the database. The transaction was rolled back',16,1, @AN)
ROLLBACK TRANSACTION

--- If not exists insert into table
BEGIN
if not exists (SELECT i.AlphanumericCol FROM inserted AS i
JOIN (SELECT AlphanumericCol FROM AMGR_User_Fields_Tbl/*table with trigger*/) AS w
ON w.AlphanumericCol = i.AlphanumericCol
GROUP BY i.AlphanumericCol, i.AlphanumericCol


What i want to know if the following things:

1. Is the trigger actually doing this?
2. Is there somehow i can incorporate a commit or rollback, somewhere in this trigger?

P.S. The reason im not using a unique constraint is because i dont want to alter the DB structure.

Sponsored Links
JR83 is offline   Reply With Quote
Reply

Tags
trigger

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
SQL Trigger Jim Microsoft Dynamics CRM 2 17-01-2008 10:42 PM
Trigger Won't Run garyh Microsoft SQL Server 137 08-12-2007 02:31 AM
Trigger Help Johnmichael Monteith Microsoft SQL Server 1 15-11-2007 03:17 AM
Trigger Help Johnmichael Monteith Microsoft SQL Server 0 15-11-2007 12:55 AM
Trigger shapper Microsoft SQL Server 2 25-10-2007 12:22 AM


< Home - Windows Help - MS Office Help - Hardware Support >


New To Site? Need Help?

All times are GMT. The time now is 07:59 AM.


vBulletin, Copyright ©2000 - 2017, Jelsoft Enterprises Ltd.
Copyright © 2005-2016, TechTalkz.com. All Rights Reserved - Privacy Policy
Valid XHTML 1.0 Transitional