Does a table need to have a primary key in order to implement a trigger?

Question

Post reply

Does a table need to have a primary key in order to implement a trigger?

smithsp17

Mr or Mrs. 500

Points: 515
More actions
September 23, 2009 at 2:37 pm

#226479

I have to update some columns in a table when they are inserted with missing values, and the value that I have to update them with is dependent on other values for the newly inserted record. This table doesn't have a unique key or primary key.

The examples I've seen use a join between the table and the inserted table to update the correct record, joining on the primary key. Is a primary key required in order to join the table with the inserted table (or deleted table, if doing update)?

Thanks,

Steve

Viewing 9 posts - 1 through 8 (of 8 total)

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

psvirin Mr or Mrs. 500 Points: 542 More actions · Answer 1

Frankly talking joins can be made on the columns not included in the unique indexes or primary keys. But in this case the for each row from the first table will be performed complete table scan of the second. And this is painfully long. Thus those indexes are used for giving uniqueness to the rows and speeding up the search process.

-------------------

StarWind Software developer ( http://www.starwindsoftware.com )

Ian Scarlett SSC-Insane Points: 23197 More actions · Answer 2

Leaving aside any arguments/discussions about having/not having a primary key on every table...

No, you don't need to have a primary key to use a trigger.

What you do need, is some way of joining the inserted table to the base table in order to identify the row(s) you want to update in the trigger. If you don't have a primary key, how can you guarantee that you will join to the correct row or that you won't update more rows than you should?

The reason you will have seen trigger examples using the primary key to join is for that exact reason... it can be used to uniquely identify the row you want to update.

Having a primary key on a table is about more than just using it in a trigger... but that's another topic entierly.

smithsp17 Mr or Mrs. 500 Points: 515 More actions · Answer 3

smithsp17

Mr or Mrs. 500

Points: 515

September 24, 2009 at 6:09 am

#1057954

Thank you both, that's what I figured. It gives me another reason to ask that we put a primary key on the table, as I didn't find any columns that could guarantee uniqueness.

Jan Van der Eecken SSCrazy Eights Points: 9013 More actions · Answer 4

Steve-437536 (9/24/2009)

Thank you both, that's what I figured. It gives me another reason to ask that we put a primary key on the table, as I didn't find any columns that could guarantee uniqueness.

I'm not quite following now. As Ian pointed out already, the presence of a primary key is not a pre-requisite for having a trigger. But now you are saying that you cannot find any columns that would make a row unique, therefore you want to put a primary key on the table? Remember, a primary key HAS to be unique. Are you saying that your trigger would enforce that uniqueness? That won't work unless the trigger is an "INSTEAD OF INSERT/UPDATE" one that fills in the other columns that will guarantee uniqueness. So it would be very much appreciated if you could post exactly what it is you are trying to achieve, together with your table definitions and some test data.

--------------------------------------------------------------------------
A little knowledge is a dangerous thing (Alexander Pope)
In order for us to help you as efficiently as possible, please read this before posting (courtesy of Jeff Moden)[/url]

smithsp17 Mr or Mrs. 500 Points: 515 More actions · Answer 5

No, I don't want the trigger to enforce uniqueness. The sole purpose of this trigger will be to update a column in which there isn't a value, based on some other values in the table. Here's some test data, and conditions that I'm checking for are

1. set [class start date] = '1/26/2009' where campus = 'ONL' and TERM = '1094' and [class start date] = ' '

2. set [class end date] = '5/20/2009' where campus = 'ONL' and TERM = '1094' and [class end date] = ' '

3. set [class start date] = '9/2/2009' where campus = 'ONL' and TERM = '1101' and [class start date] = ' '

4. set [class end date] = '12/22/2009' where campus = 'ONL' and TERM = '1101' and [class end date] = ' '

5. set [class start date] = '1/25/2010' where campus = 'ONL' and TERM = '1104' and [class start date] = ' '

5. set [class end date] = '5/19/2010' where campus = 'ONL' and TERM = '1104' and [class end date] = ' '

This is the trigger code I've been working on, and having difficulty trying to handle multi-row updates and inserts:

CREATE TRIGGER #ClassSChedule_TR_AIU] ON #ClassSchedule

AFTER INSERT, UPDATE

AS

SET NOCOUNT ON

SET XACT_ABORT ON

SET ARITHABORT ON

DECLARE

@v_ClassStartDate NVARCHAR(255),

@v_ClassEndDate NVARCHAR(255)

/*

@v_ClassStartDate_1094 NVARCHAR(255) = '1/26/2009',

@v_ClassEndDate_1094 NVARCHAR(255) = '5/20/2009',

@v_ClassStartDate_1101 NVARCHAR(255) = '9/2/2009',

@v_ClassEndDate_1101 NVARCHAR(255) = '12/22/2009',

@v_ClassStartDate_1104 NVARCHAR(255) = '1/25/2010',

@v_ClassEndDate_1104 NVARCHAR(255) = '5/19/2010'

*/

IF EXISTS(select * from deleted )

-- Update DML

IF deleted.Campus = 'ONL' and deleted.ClassStartDate = ' ' THEN

IF deleted.TERM = '1094'

@v_ClassStartDate = '1/26/2009',

@v_ClassEndDate = '5/20/2009'

ELSE

IF deleted.TERM = '1101' THEN

@v_ClassStartDate = '9/2/2009',

@v_ClassEndDate = '12/22/2009'

ELSE

IF deleted.TERM = '1104' THEN

@v_ClassStartDate = '1/25/2010',

@v_ClassEndDate = '5/19/2010'

-- This is the code I'm having difficulty trying to write, in order to process multi-row updates properly

--UPDATE #ClassSChedule

--SET c.ClassStartDate = @v_ClassStartDate,

-- c.ClassEndDate = @v_ClassEndDate

ELSE

-- Insert DML

IF inserted.Campus = 'ONL' and inserted.ClassStartDate = ' ' THEN

IF inserted.TERM = '1094'

@v_ClassStartDate = '1/26/2009',

@v_ClassEndDate = '5/20/2009'

ELSE

IF inserted.TERM = '1101' THEN

@v_ClassStartDate = '9/2/2009',

@v_ClassEndDate = '12/22/2009'

ELSE

IF inserted.TERM = '1104' THEN

@v_ClassStartDate = '1/25/2010',

@v_ClassEndDate = '5/19/2010'

-- This is the code I'm having difficulty trying to write, in order to process multi-row inserts properly

--UPDATE #ClassSChedule

--SET c.ClassStartDate = @v_ClassStartDate,

-- c.ClassEndDate = @v_ClassEndDate

And the DDL & DML follows:

IF OBJECT_ID('TempDB..#ClassSchedule','U') IS NOT NULL

DROP TABLE #ClassSchedule

CREATE TABLE #ClassSchedule (

[Course Id] [varchar](6) NULL,

[Term] [varchar](50) NULL,

[Campus] [nvarchar](255) NULL,

[Class Start Date] [nvarchar](255) NULL,

[Class End Date] [nvarchar](255) NULL

)

GO

INSERT INTO #ClassSchedule

SELECT '001009','1094','BRB','1/26/2009','5/20/2009' UNION ALL

SELECT '001031','1094','BRB','1/26/2009','5/20/2009' UNION ALL

SELECT '001077','1094','BRB','1/26/2009','5/20/2009' UNION ALL

SELECT '001081','1094','BRB','1/26/2009','5/20/2009' UNION ALL

SELECT '003117','1094','BRB','1/26/2009','5/20/2009' UNION ALL

SELECT '001116','1094','BRB','1/26/2009','5/20/2009' UNION ALL

SELECT '001124','1094','BRB','1/26/2009','5/20/2009' UNION ALL

SELECT '001192','1094','BRB','1/26/2009','5/20/2009' UNION ALL

SELECT '001218','1094','BRB','1/26/2009','5/20/2009' UNION ALL

SELECT '001223','1094','BRB','1/26/2009','5/20/2009' UNION ALL

SELECT '001651','1094','BRB','1/26/2009','5/20/2009' UNION ALL

SELECT '001309','1094','BRB','1/26/2009','5/20/2009' UNION ALL

SELECT '001312','1094','BRB','1/26/2009','5/20/2009' UNION ALL

SELECT '001350','1094','BRB','1/26/2009','5/20/2009' UNION ALL

SELECT '001351','1094','BRB','1/26/2009','5/20/2009' UNION ALL

SELECT '001354','1094','BRB','1/26/2009','5/20/2009' UNION ALL

SELECT '004060','1101','MTH','9/2/2009','12/22/2009' UNION ALL

SELECT '001509','1101','MTH','9/2/2009','12/22/2009' UNION ALL

SELECT '001106','1104','ONL','1/25/2010','5/19/2010' UNION ALL

SELECT '001300','1104','ONL','1/25/2010','5/19/2010' UNION ALL

SELECT '001106','1104','ONL','1/25/2010','5/19/2010' UNION ALL

SELECT '001300','1104','ONL','1/25/2010','5/19/2010' UNION ALL

SELECT '002989','1104','ONL','1/25/2010','5/19/2010' UNION ALL

SELECT '001106','1104','ONL','1/25/2010','5/19/2010' UNION ALL

SELECT '001634','1101','ONL','9/2/2009','12/22/2009' UNION ALL

SELECT '001001','1101','ONL','9/2/2009','12/22/2009' UNION ALL

SELECT '001512','1101','ONL','9/2/2009','12/22/2009' UNION ALL

SELECT '001106','1104','ONL','1/25/2010','5/19/2010' UNION ALL

SELECT '001633','1101','ONL','9/2/2009','12/22/2009' UNION ALL

SELECT '001634','1101','ONL','9/2/2009','12/22/2009' UNION ALL

SELECT '001548','1101','ONL','9/2/2009','12/22/2009' UNION ALL

SELECT '002989','1104','ONL','1/25/2010','5/19/2010' UNION ALL

SELECT '001688','1101','RCK','9/2/2009','12/22/2009' UNION ALL

SELECT '001704','1101','RCK','9/2/2009','12/22/2009' UNION ALL

SELECT '001704','1101','RCK','9/2/2009','12/22/2009'

Jan Van der Eecken SSCrazy Eights Points: 9013 More actions · Answer 6

Hi Steve,

It is a bit late (in this part of the world) to wrap my head around you problem, but what first comes to mind is that your table is certainly not in 3rd Normal Form if the values of certain columns can be derived from other columns within the same table. I'd have a look at redesigning that if I were you. Secondly, I guess the code you posted using a temporary table isn't what your real database looks like? I've never seen anyone put a trigger on a temp table, in fact, I'm not even sure if that's possible at all. Will have a look at your issue again tomorrow morning when I'm hopefully more awake thaqn now.

Regards,

Jan

--------------------------------------------------------------------------
A little knowledge is a dangerous thing (Alexander Pope)
In order for us to help you as efficiently as possible, please read this before posting (courtesy of Jeff Moden)[/url]

smithsp17 Mr or Mrs. 500 Points: 515 More actions · Answer 7

Hi Jan,

Yes, you're right, it's not a temporary table, and the real table has many more columns, as it's actually part of a data mart, an extraction from an Oracle database that for some reason doesn't always populate the ClassStartDate and ClassEndDate columns. The developer who set this up has it set to extract from the Oracle database every hour to a text file, then has a SQL Server job set up to load it into a SQL Server table. This keeps users off the Oracle database, but allows them to go after the data in the SQL Server table.

Currently he runs the job to fix any missing dates 5 minutes after the job that loads the SQL Server table, based on the criteria I have given in my earlier posting. The problem that we've been seeing is that sometimes the load takes longer than 5 minutes, and causes the job to fix the missing dates to fail, which in turn causes bad data. That's why I thought a trigger would be better, which would check the values of the missing dates and populate them. But now that I've laid this out for you, I'm thinking to myself, why make it part of a trigger, why not just add a 2nd step to the first job that populates the table in the first place, which will fix the missing dates? It makes sense to be part of the same job, then it will always be guaranteed to never overlap! The reload of the table thru a job is currently the only way the table is ever inserted into, and is never updated, so this should work fine.

I'll make the change and post the results, thanks for getting me to think some more about it.

Steve

Jan Van der Eecken SSCrazy Eights Points: 9013 More actions · Answer 8

Hi Steve,

I'd say first prize would be if you could get the extraction to populate the ClassStart/EndDate columns correctly in all cases. May be easier said than done, I know, but it will save you from the headaches getting those right afterwards.

Adding a second step to the job may not be a bad idea, but since you are saying that the first job runs once an hour and may not finish in 5 minutes, what if that second step doesn't finish in time either for the next job iteration starting off?

Just as an aside, how does the data get from Oracle to SQL? Extractions to file on the one side and bulk loads on the other? Or are you just linking through to the Oracle machine?

Re the issues with multi-row inserts and updates in triggers, you should just not assume that the INSERTED and DELETED pseudo tables available in the trigger consist of a single row only, which is what your sample trigger does when it says

IF deleted.Campus = 'ONL' and deleted.ClassStartDate = ' '

You'll have to use set-based logic to achieve what you need to do, especially in a trigger where you don't know what kind of modifications to the tables are gonna be thrown at you in one go. There's lots of sample code on SSC and in BOL that show you how to handle multi-row transactions in triggers correctly.

And the most important thing, don't hard-code rules in triggers as you showed above, rather put rules like the ones you mentioned into a separate table and again use set-based logic to update the missing data. If you do hard-code them you will get yourself into a permanent maintenance headache whenever a rule changes or is added.

Keep us updated as to how you went on, and come back if you have more questions.

Greets,

Jan

--------------------------------------------------------------------------
A little knowledge is a dangerous thing (Alexander Pope)
In order for us to help you as efficiently as possible, please read this before posting (courtesy of Jeff Moden)[/url]