Problems with writing Triggers

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

Problems with writing Triggers

Author

Topic

Justice Gundam
Starting Member

5 Posts

Posted - 2010-02-17 : 04:17:40

Good morning. I'm a newcomer in this forum, and I thought I should ask you a question to come out of a bind I'm finding myself in.

I'm beginning to learn SQL Server programming using SQL Server 2005 Express, and as of now, I'm using Management Studio Express to create a database. What gives me trouble is the fact that I can't manage to write a Trigger for the database I'm creating - more to the point, I can't manage to make a Trigger work for the associated table. Even after I've written it, I can see no proof that my Trigger is actually working. Could anybody help me with that?

I appreciate any help, and thank you for your answers.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-17 : 04:21:25

what trigger have you written and on which table and for which action? you need to do the same action on the table and then check results as per logic you've written in trigger. for more help, post your trigger code.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-02-17 : 04:25:35

Can you post your trigger code?

Your description is a little too vague to offer real help.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Justice Gundam
Starting Member

5 Posts

Posted - 2010-02-17 : 04:29:30

Okay, this is the code I've written for the trigger. I should probably mention that I've written it for a sample table with the following fields: ID_Number, Name, Adress and Age, and that I've marked the ID_Number as the primary key. I'm trying to make it so that the database warns you if you don't specify Age, but as now I've had no results.

CREATE TRIGGER [dbo].[ProvaTrigger]
ON [dbo].[TabellaID]
AFTER INSERT
AS
BEGIN

SET NOCOUNT ON;

DECLARE @age INT
SELECT @age = TabellaID.Age FROM TabellaID
IF (@age = NULL)
BEGIN
PRINT 'You must provide age';
ROLLBACK;

END

END

If there are more information you need, please tell me.

Thank you very much.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-17 : 04:37:25

quote:
Originally posted by Justice Gundam

Okay, this is the code I've written for the trigger. I should probably mention that I've written it for a sample table with the following fields: ID_Number, Name, Adress and Age, and that I've marked the ID_Number as the primary key. I'm trying to make it so that the database warns you if you don't specify Age, but as now I've had no results.

CREATE TRIGGER [dbo].[ProvaTrigger]
ON [dbo].[TabellaID]
AFTER INSERT
AS
BEGIN

SET NOCOUNT ON;

DECLARE @age INT
SELECT @age = TabellaID.Età FROM TabellaID
IF (@age = NULL)
BEGIN
PRINT 'You must provide age';
ROLLBACK;

END

END

Maybe you've did this to learn trigger, but seeing your code just wanted to tell you that the above is not purpose for which trigger is designed. What you're doing above are client side validation which has to be done at your presentation layer using client side validation languages like jquery,javascript etc. Triggers are mostly used to impose business rules during DML operations on a table or for capturing of audit/history of data changes in table.
Also by selecting from table in trigger you're selecting entire data always. If the only data you're interested is recently inserted ones, they can be got from internal table called INSERTED.
Actually triggers work by means of two internal tables INSERTED & DELETED
for insert action, values inserted can be retrived from INSERTED
for delete action, deleted values can be retrieved from DELETED
for update action, the original values can be retrieved from DELETED and updated (new) values can be got from INSERTED
so it should be

SELECT TabellaID.Età FROM INSERTED

another point is you may have multiple records in INSERTED,DELETED at a time (consider batch inserts/updates/deletes) so you cant store all values in a variable. you should rather be storing them in table variable like

INSERT @Temp
SELECT TabellaID.Età FROM INSERTED

finally you cant check for NULLs using IF (@age = NULL) under default settings as NULL is not stored as a value internally but it just represents state of unknown or undefined value. so use

IF (@age IS NULL) for null checks

Also read below link to know more on triggers and their usage
http://msdn.microsoft.com/en-us/library/ms189799.aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-02-17 : 04:52:04

Also -- just declare the age column on the Table with the NOT NULL keyword.

Then you *can't* insert or update a row to have a null value.

As visakh16 says: this isn't want you should use a trigger for.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Justice Gundam
Starting Member

5 Posts

Posted - 2010-02-17 : 05:26:13

Hmm... okay, so could you help me a little here? For example, let's say I don't want any age below 14 and would like for any age below that to be changed to 14 by default. How do I do that?

And... yes, I'm a beginner. I started studying this about... well, three days ago!

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-02-17 : 05:34:38

Well for starters I probably wouldn't have an age column. (unless it was a computed column).

I'd have a date of birth column instead. That doesn't change while an age column does.

I'd declare it NOT NULL and put a CHECK CONSTRAINT on it.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-17 : 05:36:50

quote:
Originally posted by Justice Gundam

Hmm... okay, so could you help me a little here? For example, let's say I don't want any age below 14 and would like for any age below that to be changed to 14 by default. How do I do that?

And... yes, I'm a beginner. I started studying this about... well, three days ago!

then just do like


CREATE TRIGGER [dbo].[ProvaTrigger]
ON [dbo].[TabellaID]
AFTER INSERT,UPDATE
AS 
BEGIN

SET NOCOUNT ON;

IF EXISTS (SELECT 1 FROM INSERTED WHERE age<14)
BEGIN
UPDATE t
SET t.age=14
FROM [dbo].[TabellaID] t
JOIN INSERTED i
ON i.ID_Number=t.ID_Number
WHERE i.age<14
END
END

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-02-17 : 05:42:57

I still think storing an age column isn't good form.

Here's a stab at a possible table structure


IF OBJECT_ID('tempDb..#testTable') IS NOT NULL DROP TABLE #testTable
GO

CREATE TABLE #testTable (
	[Id] INT IDENTITY (1,1)
	, [firstname] VARCHAR(255)
	, [surname] VARCHAR(255)
	, [dateOfBirth] DATETIME NOT NULL

	CONSTRAINT [TT_Check_DOB] CHECK ([dateOfBirth] <= '20060217')
	PRIMARY KEY NONCLUSTERED ([ID])
	)

CREATE CLUSTERED INDEX IX_TESTTAB_SURNAME_FIRSTNAME ON #testTable ([surname], [firstname])
GO

With this the first statement following will work and the second would fail


INSERT #testTable ([firstname], [surname], [dateOfBirth])
      SELECT 'Fred', 'Bloggs', '19120101'
UNION SELECT 'Adam', 'Clogg', '19950203'

-- This one will fail
INSERT #testTable ([firstname], [surname], [dateofBirth])
      SELECT 'Foo', 'Bar', '20100101'

I'd probably rely on whatever interfaces I'd written to do validation of ages before trying to insert data but you could do as Visakh16 suggested with a trigger to fix the dateofbirth below a certain date.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-17 : 05:49:35

I would also recommend Charlies approach to do this at client side.
Just showed you how could do this if you were forced to do at SQL end using triggers

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Kristen
Test

22859 Posts

Posted - 2010-02-17 : 05:55:00

" let's say I don't want any age below 14 and would like for any age below that to be changed to 14 by default."

Notwithstanding the excellent advice the others have given, can I question the logic of this?

If I say that I am 12 you will change the database record to show that I am 14?

The logic of that seems a bit strange to me!

Justice Gundam
Starting Member

5 Posts

Posted - 2010-02-17 : 06:00:24

It was just an example. This database didn't serve any practical purpose, I was doing it to have more practical experience with SQL Server 2005 Express.

Thanks, guys, I'll definitely try your solutions out! I may come back to this thread tomorrow if there are more problems though...

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-02-17 : 06:18:44

you might want to look at it this way:

in general it should be the database's job to accept data and to enforce the business rules for that data. In your case:

Age > 14 (dob < today - 14 years). And not null.

That's the check constraint's job.

It's not generally good form to have the database make decisions about what to do with bad data -- it's better for the database just to say NO and then you deal with that NO response in your application layer.

With triggers you can make a real mess, really fast.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-17 : 06:25:12

yeah.. as Charlie told in such case a simple check constraint on age field will suffice

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Kristen
Test

22859 Posts

Posted - 2010-02-17 : 06:56:06

" It was just an example."

OK, got it. But I'm with T.C. on this one - better to store the data at its most accurate, (by all means present it "fuzzy" - e.g. "14 or Under"), otherwise you have lost some of the quality of the data, and you can't then get that back of course

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources