Help with a trigger

Question

Help with a trigger

Alasdair Thomson

Hall of Fame

Points: 3630
More actions
June 19, 2009 at 5:12 pm

#205766

Hi all,
I hope you are all well... Anyway to the point... I have a table as below:
CREATE TABLE [TANDEM].[tblPurchaseType](
[intAuthPurchaseTypeID] [BIGINT] IDENTITY (-9223372036854775808,1) UNIQUE NOT NULL,
[tmsTimeStamp] [TIMESTAMP] NOT NULL,
[intPlatformID] [int] NOT NULL,
[vchPurchaseType] [NVARCHAR](255) NOT NULL,
[vchPurchaseTypeDescription] [NVARCHAR](MAX) NOT NULL
) ON [PRIMARY]
GO
And the 2 key fields on this table, intPlatformID & vchPurchaseType and to ensure that no duplicate keys are added into the table I am wanting to create a trigger which fires for each record inserted, or updated (please note i have set foriegn keys up where appropriate). Should a user add a duplicate key I want a message advising on the error and to rollback the transaction.
Here is my attempt at the trigger:
CREATE TRIGGER trigCheckPurchaseKeys
ON [TANDEM].[tblPurchaseType]
FOR INSERT, UPDATE
AS
/* Get the range of level for this job type from the jobs table. */
DECLARE @intPlatformID AS INT;
DECLARE @vchPurchaseType AS NVARCHAR(255);
DECLARE @vchPurchaseTypeDescription AS NVARCHAR(255);
SELECT @intPlatformID = (SELECT intPlatformID FROM inserted)
SELECT @vchPurchaseType = (SELECT vchPurchaseType FROM inserted)
SELECT @vchPurchaseTypeDescription = (SELECT vchPurchaseTypeDescription FROM inserted)
IF EXISTS(
SELECTintPlatformID
FROMTANDEM.tblPurchaseType
WHEREtblPurchaseType.intPlatformID = @intPlatformID
)
BEGIN
ROLLBACK TRANSACTION
PRINT 'Empty Values Not Inserted'
END
GO
But what I when I add a valid record is the error message and the record added to the table, likewise when I add an invalid record The error message again returns and also stills adds the invalid record... What am I missing? Please help.
Thank ou in advance...

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Fraggle-805517 SSCrazy Eights Points: 8907 More actions · Answer 1

Are you saying that each of the columns below must have unique values or that a combination of the two columns must be unique?

If each column has to be unique, forget the trigger and make them a unique column. I also believe that you can do this for a combinatino outside of a primary key, but i haven't tried it, so don't quote me on that.

If a trigger is the only way you want to do it (and it isn't my recommendation), then you would need to use an "INSTEAD OF" trigger. This type of trigger essentially tells the system that before you insert/update, check my logic first.

Create Trigger

on

instead of insert, update

as

(check logic)

Aslo, I would recomment using a BEGIN/END statement to denote the start and end of the trigger.

I believe the other issue with you trigger is the rollback. It sounds like you are using an AFTER trigger (this wasn't specified in your example). This means that the row has already been instered into the table and there isn't a transaction to roll back. So you are trying to rollback something that doesn't have a begin transaction, which may be why you are getting the errors.

Check it out and let me know.

HTH,

Fraggle

Alasdair Thomson Hall of Fame Points: 3630 More actions · Answer 2

Hi thank you for your reply, to clarify the combination of these two fields must be unique not both being unique. I will look into the instead of trigger but yes I want to prevent the data being inserted until the check is done. If it passes the check, then the data is inserted, otherwise the transaction is rolled back... Hope this clarifies things.

Alasdair Thomson Hall of Fame Points: 3630 More actions · Answer 3

I figured it, I was trying to use a trigger as I thought the contraints would only work over 1 field at a time, but this is not the case...

CREATE TABLE [TANDEM].[tblPurchaseType](

[intAuthPurchaseTypeID] [BIGINT] IDENTITY (-9223372036854775808,1) UNIQUE NOT NULL,

[tmsTimeStamp] [TIMESTAMP] NOT NULL,

[intPlatformID] [int] NOT NULL,

[vchPurchaseType] [NVARCHAR](255) NOT NULL,

[vchPurchaseTypeDescription] [NVARCHAR](MAX) NOT NULL,

UNIQUE ([intPlatformID], [vchPurchaseType])

) ON [PRIMARY]

GO

So this bit gives me what I need: UNIQUE ([intPlatformID], [vchPurchaseType])

Thank you for your help!

Fraggle-805517 SSCrazy Eights Points: 8907 More actions · Answer 4

Glad to hear that you got it figured out. Would like to take some credit for leading you there, but from what it sounds like, you didn't need it.

😀

Fraggle