Trigger code syntax help please =(

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

Trigger code syntax help please =(

Author

Topic

richdiaz99
Starting Member

22 Posts

Posted - 2010-12-01 : 14:28:24

I have a table with 3 columns -OfferID, OfferName, IsActive

With three rows of data:
Offer01, March Sale,0
Offer02, April Sale,0
Offer03, May Sale,1

What I need it an insert trigger that takes the OfferName and IsActive as parameters but automatically updates the OfferID to the next sequential number: i.e. as shown in the 3rd row above Offer03 then the last inserted row, so the newly inserted row must be named Offer04

The OfferID column is the primary key.

Disclaimer, I didn't make the database ;-)

Thanks for the help!

Below is what I have so far:

USE [MyTable]
GO

SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO

ALTER TRIGGER [dbo].[trgOfferDetails] ON [dbo].[tblOfferDetails]
FOR UPDATE

AS

DECLARE @OfferID varchar(10)
DECLARE @IsActive int

SELECT
@OfferID = OfferID,
@IsActive = IsActive
FROM INSERTED

BEGIN
UPDATE tblOfferDetails
SET ????????????
WHERE ????????????
END

Many many thanks

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2010-12-01 : 15:20:51

quote:
SELECT
@OfferID = OfferID,
@IsActive = IsActive
FROM INSERTED

And what happens when more than one row is affected by the update?

Also, you talked about when a row is inserted, yet you have an After Update trigger.

--
Gail Shaw
SQL Server MVP

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2010-12-01 : 15:25:50

I don't even think a trigger will work here. A trigger occurs after the operation, but the insert will fail if the OfferID column is null (it's the pk)

How is data inserted into this table? Stored procedure?

--
Gail Shaw
SQL Server MVP

richdiaz99
Starting Member

22 Posts

Posted - 2010-12-01 : 15:43:21

quote:
Originally posted by GilaMonster

quote:
SELECT
@OfferID = OfferID,
@IsActive = IsActive
FROM INSERTED

And what happens when more than one row is affected by the update?

Also, you talked about when a row is inserted, yet you have an After Update trigger.

I don't even think a trigger will work here. A trigger occurs after the operation, but the insert will fail if the OfferID column is null (it's the pk)

How is data inserted into this table? Stored procedure?

--
Gail Shaw
SQL Server MVP

Wow, good points, thanks Gail.

Answers:

There will be only one row in each update. One new offer is added every 3 months.

"After Update trigger" is incorrect I guess =/ - what should it be?

I didn't even think about the PK being null and failing the insert all together =/ Is the only solution to add a real primary key to the table?

Stored Proc? How the data is entered has not be decided, it could be a Stored Proc but I was told to make a trigger on the table to protect it (something I've never done before)

Thanks so much!

TimSman
Posting Yak Master

127 Posts

Posted - 2010-12-01 : 15:50:46

A trigger to "protect it"? What does that mean exactly?

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2010-12-01 : 15:51:18

If you can change the design of the table, that would be best. Then you can have an identity column (that's the real primary key) and a computed column that shows the 'Offer04' value. No triggers necessary.

If you want a trigger to fire after insert, it's a for/after insert trigger.

Just because there's only one row added at a time now doesn't mean that will be true in 6 months time. Intentionally coding a bug into a trigger is a silly thing to do.

--
Gail Shaw
SQL Server MVP

richdiaz99
Starting Member

22 Posts

Posted - 2010-12-02 : 10:24:32

Thanks! :)

Looks like I may not be allow to change the table :(

And my boss insists the "Before" trigger will accept a NULL PK so I'll have to try it.... but I'm not sure how to code the SQL Syntax, I'm a C# programmer =/

BEGIN
UPDATE tblOfferDetails
SET ????????????
WHERE ????????????
END

oh, the multiple row INSERT issue you brought up, would it be possible to ignore all but the first row inserted?

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2010-12-02 : 10:47:02

A before (instead of) trigger intercepts the insert. You can then chack and make changes and need to repeat the insert within the trigger.

create trigger tr_tblOfferDetails on tblOfferDetails instead of insert
as
insert tblOfferDetails (...)
select ....
from inserted
go

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2010-12-02 : 11:45:39

Why are you not allowed to change the design? It's really going to make things far more complex than necessary.

--
Gail Shaw
SQL Server MVP

SparkByte
Yak Posting Veteran

60 Posts

Posted - 2010-12-09 : 14:35:00

How about something like this?

You will need to add something to handle the leading zero on numbers less than 10.

Set NoCount On
Declare @Test Table 
(
		PK Int,
		OfferID VarChar(10),
		OfferName VarChar(12),
		IsActive bit
)
Insert Into @Test Values(1,'Offer1','Jan',0)
Insert Into @Test Values(2,'Offer2','Feb',0)
Insert Into @Test Values(3,'Offer1','Jan',0)
Insert Into @Test Values(4,'Offer2','Feb',0)
Insert Into @Test Values(5,'Offer3','Mar',0)
Insert Into @Test Values(6,'Offer4','Apr',1)
;

--	Select Max(PK)+1 from @Test
--
--	Select 'Offer' + Cast(Max(dbo.fnGetNumbers(OfferID))+1 As VarChar(1)) As OfferID 
--	from @Test 
--	where IsActive = 1

Declare @Next Int
Insert Into @Test
	Select 
		(Select Max(PK)+1 from @Test) As PK,
		(Select 'Offer'+Cast(Max(dbo.fnGetNumbers(OfferID))+1 As VarChar(1)) As OfferID 
		from @Test 
		where IsActive = 1) As OfferID,
		'May',
		0


Select * from @Test

Thank You,

John Fuhrman
http://www.titangs.com

SparkByte
Yak Posting Veteran

60 Posts

Posted - 2010-12-09 : 15:14:26

BTW, Your BOSS is incorrect about the primary Key field.

quote:
SQL PRIMARY KEY Constraint
The PRIMARY KEY constraint uniquely identifies each record in a database table.

Primary keys must contain unique values.

A primary key column cannot contain NULL values.

Each table should have a primary key, and each table can have only ONE primary key

Most Primary Key Fields are either auto incrementing or calculated fields and you do not set a value for them during the "Before" trigger. They will get their value set automagicly during the insert.

Thank You,

John Fuhrman
http://www.titangs.com

Kristen
Test

22859 Posts

Posted - 2010-12-09 : 16:11:17

"Most Primary Key Fields are either auto incrementing or calculated fields and you do not set a value for them during the "Before" trigger. They will get their value set automagicly during the insert."

Oh Dear! All my software is wrong then

"There will be only one row in each update."

If you build a trigger to only handle a single row (which I personally think is a bad idea) then make sure that it raises an error if it is ever given multiple rows - e.g. IF (SELECT COUNT(*) FROM inserted) > 1 RAISERROR(...) and rollback the transaction

If you need to calculate a value for the Primary Key then your best choices are probably:

1. Use a Stored Procedure to create the record. Encapsulate whatever logic is required to manipulate the columns, including lookup up / calculating the PK column value(s)

2. Use an INSTEAD OF (before) trigger to do that work

Personally I think that INSTEAD OF triggers are a bit of a nuisance. We have had problems with side effects from them in the past (not being able to use SELECT *, so that they are immune to column additions in the future, if the table includes Computed Columns and issues involving ARTIH_ABORT settings), so my preference would be with a Stored Procedure.

Notwithstanding that you said that the trigger would only process a single row, a key benefit of an INSTEAD OF trigger would be that you could easily bulk-import data into the table and the trigger would take care of the PK (whereas with a Stored Procedure you will have to loop round calling the Sproc once-per-row, or make the Sproc able to handle a set of data to be inserted - which will normally mean increasing the complexity of the corresponding multi-row bulk-import to match)

SparkByte
Yak Posting Veteran

60 Posts

Posted - 2010-12-09 : 16:37:14

I think I was misunderstood.

What I was trying to point out was that in the stored procedure or Instead of trigger you cannot assign a NULL value to the primary key, unless you disable the table constraint to allow it, not that the value could not be created/calculated into the needed value prior to the insert.

Thank You,

John Fuhrman
http://www.titangs.com

Kristen
Test

22859 Posts

Posted - 2010-12-09 : 16:49:34

Not sure I follow your point, but I appreciate the clarification.

I expect we all agree that:

It is possible to Insert NULL into a Primary Key field provided that an INSTEAD OF trigger will "sort it out" and provide a NOT NULL value that is actually inserted into the table. This is not possible with an AFTER trigger (because the record had already been inserted into the table before the trigger fires which, in the case of a NULL PK value, would have failed)

SparkByte
Yak Posting Veteran

60 Posts

Posted - 2010-12-09 : 17:32:48

Yes, that was what I was trying to say.

Thank You,

John Fuhrman
http://www.titangs.com

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources