How to create trigger - 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 2008 Forums

Transact-SQL (2008)

How to create trigger

Author

Topic

duanecwilson
Constraint Violating Yak Guru

273 Posts

Posted - 2010-07-14 : 12:00:43

I have this table:

CREATE TABLE [msdn].[CVR_temp](
	[DATA_SOURCE] [varchar](4) NOT NULL,
	[AS_OF_DATE] [char](6) NULL,
	[SEQUENCE_NUMBER] [int] IDENTITY(1,1) NOT NULL,
	[COST_DRIVER_ID] [varchar](9) NOT NULL,
	[ADJUSTMENT_CODE] [varchar](1) NULL,
	[QUANTITY] [int] NULL,
	[BILL_FLAG] [varchar](1) NOT NULL,
	[ENTITYAU_ID] [varchar](11) NOT NULL,
	[APPLICATION_ID] [varchar](1) NOT NULL,
	[EMPLID] [varchar](255) NULL,
	[LASTNAME] [nvarchar](255) NULL,
	[FIRSTNAME] [nvarchar](255) NULL,
	[EMAIL] [nvarchar](255) NULL
) ON [PRIMARY]

The [ENTITYAU_ID] column should have 5 asterisks followed by 7 numerical digits padded with leading zeroes to make it 7. So it should look like this '*****0012345'. The data that comes in is just the asterisks and the number, but it is sometimes shorter than 7. I tried to create a trigger when inserted or updated, but couldn't get anything close. For one thing, I think the SEQUENCE_NUMBER (being an identity) is giving me problems, but that's not all. I don't know how to code it. The following expression does what I want to do on the column but I don't know how to put it in a trigger:

REPLACE([ENTITYAU_ID],[ENTITYAU_ID],	'*****' + RIGHT ('0000000'+ CAST ([ENTITYAU_ID] AS varchar), 7) )

I want an insert trigger and an update trigger. Even if there is a better solution, I want to learn how to do these.

Duane

robvolk
Most Valuable Yak

15732 Posts

Posted - 2010-07-14 : 12:19:53

You don't need a trigger for this at all, or even a persisted column:

CREATE TABLE [msdn].[CVR_temp](
	[DATA_SOURCE] [varchar](4) NOT NULL,
	[AS_OF_DATE] [char](6) NULL,
	[SEQUENCE_NUMBER] [int] IDENTITY(1,1) NOT NULL,
	[COST_DRIVER_ID] [varchar](9) NOT NULL,
	[ADJUSTMENT_CODE] [varchar](1) NULL,
	[QUANTITY] [int] NULL,
	[BILL_FLAG] [varchar](1) NOT NULL,
	[ENTITYAU_ID] AS '*****' + REPLACE(STR(SEQUENCE_NUMBER,7,0),' ','0'),
	[APPLICATION_ID] [varchar](1) NOT NULL,
	[EMPLID] [varchar](255) NULL,
	[LASTNAME] [nvarchar](255) NULL,
	[FIRSTNAME] [nvarchar](255) NULL,
	[EMAIL] [nvarchar](255) NULL
) ON [PRIMARY]

edit: even though you want/need to learn triggers, this is not a good situation to use one.

KrafDinner
Starting Member

34 Posts

Posted - 2010-07-14 : 12:29:05

[code]
CREATE TRIGGER triggerName123
ON msdn.CVR_temp
FOR INSERT
AS

UPDATE msdn.CVR_temp
SET ENTITYAU_ID = '*****' + RIGHT ('0000000'+ CAST ([ENTITYAU_ID] AS varchar), 7)
WHERE SEQUENCE_NUMBER = (SELECT SEQUENCE_NUMBER FROM inserted)
[/code]

That should handle your insert. I'm not exactly sure about the process for the update to avoid a recursive trigger in an infinite loop. This is what I would guess, but I honestly don't know for sure.

[code]
CREATE TRIGGER triggerName123
ON msdn.CVR_temp
FOR INSERT, UPDATE
AS

--Check to see if the ENTITYAU_ID field is different from the one prior to the update.
-- If not, update it.
IF UPDATE(ENTITYAU_ID) AND (SELECT ENTITYAU_ID FROM inserted) NOT LIKE (SELECT ENTITYAU_ID FROM deleted)
UPDATE msdn.CVR_temp
SET ENTITYAU_ID = '*****' + RIGHT ('0000000'+ CAST ([ENTITYAU_ID] AS varchar), 7)
WHERE SEQUENCE_NUMBER = (SELECT SEQUENCE_NUMBER FROM inserted)
[/code]

I'm just sort of stabbing in the dark here because I'm certainly no expert, but this is my guess.

KrafDinner
Starting Member

34 Posts

Posted - 2010-07-14 : 12:33:12

robvolk, I don't think he was wanting to use the SEQUENCE_NUMBER as the ENTITYAU_ID field. I didn't even know your suggestion was possible, though, so that is definitely something I would have to look into - that seems a very neat way to ensure data is in the fields the way they should be. Could a constraint be set up to do this ? (or is that what your syntax does?)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources