Need help with Triggers - 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

General SQL Server Forums

New to SQL Server Programming

Need help with Triggers

Author

Topic

blammo04
Starting Member

7 Posts

Posted - 2009-11-18 : 18:45:51

I need some help with creating triggers.

I have a table that needs a trigger that retricts the commission field in the table so that the commission is <= 500 and >= 150. The trigger should fire on insert and update and should also give a error message if the commision is not within the boundaries.

This is the table I have ....

create table CrewAssignments(
boatname varChar2(20),
planneddepart varChar2(20),
crewssn number(6),
role varchar2(20),
commission number(10),
PRIMARY KEY (boatname, planneddepart, crewssn),
FOREIGN KEY (boatname, planneddepart) references Charters(boatname, planneddepart),
FOREIGN KEY (crewssn) references Crew (crewssn),
FOREIGN KEY (role) references Role(role));

This is the Trigger that I have attempted to create but I need some major help with it.

CREATE TRIGGER crewTrigger
BEFORE insert OR update of commission ON crewAssignments
DECLARE
.........
BEGIN
.........
IF(commission > 500 || commission < 150)
" THE COMMISSION IS INCORRECT"

END;

Thats what I have but I know that it is not correct. IF anyone could help I would greatly appreciate it. Thank you.

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2009-11-18 : 19:56:57

First, this should be a check constraint, not a trigger.

Second, a logical or in SQL Server is "OR" not "||"


create table CrewAssignments(
	boatname varChar(20),
	planneddepart varChar(20),
	crewssn int,
	role varchar(20),
	commission int,
	PRIMARY KEY (boatname, planneddepart, crewssn),
	FOREIGN KEY (boatname, planneddepart) references Charters(boatname, planneddepart),
	FOREIGN KEY (crewssn) references Crew (crewssn),
	FOREIGN KEY (role) references Role(role),
	Constraint ck_crewAssignments_commission Check (Commission between 150 and 500)
);

also, this looks suspiciously like DB2 or maybe Oracle syntax...should use SQL Server data types if this is for SQL Server

blammo04
Starting Member

7 Posts

Posted - 2009-11-18 : 20:23:34

create table crewAssignments (
boatName varchar2(30),
plannedDepart date,
crewSSN char(11) constraint crewAssignments_crewSSN_FK
references crew(crewSSN),
role varchar2(30) constraint crewAssignments_role_FK
references roles(role),
commission number(8,2) constraint crewAssignments_commission_CK
check(commission >= 0.0),
constraint crewAssignment_FK
foreign key(boatName,plannedDepart) references charters (boatName,plannedDepart),
constraint crewAssignment_PK
primary key(boatName,plannedDepart,crewSSN));

Would this syntax be correct?

And I would use the check restraint but I cant. I have to use a trigger instead.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-11-18 : 21:05:06

quote:
Originally posted by blammo04

And I would use the check restraint but I cant. I have to use a trigger instead.

Why? Is this for a homework assignment?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

blammo04
Starting Member

7 Posts

Posted - 2009-11-18 : 21:34:30

yes it is, we are going over triggers now and thats why it has to be a trigger.

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2009-11-18 : 22:24:36

lol. well think how smart you'll look when you tell the professor that you decided the right way to do it is with a constraint and not a trigger lololol

blammo04
Starting Member

7 Posts

Posted - 2009-11-18 : 22:47:33

Does anyone know if this is close to being right?

CREATE OR REPLACE TRIGGER crewTrig1
BEFORE INSERT OR UPDATE ON crewAssignments
BEGIN
IF commission > 500
OR commission < 150
RAISE_APPLICATION_ERROR (0, 'Commission is not in boundaries')
END IF;
END;

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2009-11-18 : 23:53:06

you're close. since it's homework, most of won't just hand it to you. but have a look here: http://technet.microsoft.com/en-us/library/ms189799.aspx

if you scroll down, they have some samples (the syntax at the top is a bit cryptic for students)

Let us know if that put you where u need to be. Tell ya what, look at that, fix your (minor) errors and post what you come up with.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-11-18 : 23:54:18

Are you using Microsoft SQL Server or MySql?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

peterkirubakaran
Starting Member

12 Posts

Posted - 2009-11-18 : 23:56:04

CREATE TRIGGER crewTrig1 ON crewAssignments FOR INSERT,UPDATE
AS
BEGIN
DECLARE @commission INT
SET @commission=(SELECT commission FROM INSERTED)
IF (@commission>500 OR @commission<150)
BEGIN
RAISERROR('Commission is not in boundaries',16,1)
ROLLBACK TRAN
END
END

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2009-11-19 : 00:57:02

quote:
Originally posted by russell

since it's homework, most of won't just hand it to you.

So much for that...

of course the trigger shown won't work if multiple records are updated...

prakum
Starting Member

16 Posts

Posted - 2009-11-19 : 02:00:34

why it will not work for multiple records????

Praveen Kumar

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-11-19 : 02:08:50

quote:
Originally posted by prakum

why it will not work for multiple records????

Praveen Kumar

Because the trigger will fail when it hits this line of your code:
SET @commission=(SELECT commission FROM INSERTED)

See this blog for more info: http://weblogs.sqlteam.com/tarad/archive/2004/09/14/2077.aspx

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

peterkirubakaran
Starting Member

12 Posts

Posted - 2009-11-19 : 03:16:04

Thanks tkizer for your valuable info

prakum
Starting Member

16 Posts

Posted - 2009-11-19 : 03:23:46

hello tkizer

in this blog "http://weblogs.sqlteam.com/tarad/archive/2004/09/14/2077.aspx" that you mentioned has below solution but i have a question in this.....

Q:Where is t1.somecolumn refers to.....bec you not created any alias name as T1.????
Explain this for me.....

CREATE TRIGGER trg_Table1
ON Table1
FOR UPDATE
AS

UPDATE t2
SET SomeColumn = t1.SomeColumn
FROM Table2 t2
INNER JOIN inserted i
ON t2.Table1_ID = i.Table1_ID

thanks

Praveen Kumar

blammo04
Starting Member

7 Posts

Posted - 2009-11-19 : 10:00:51

CREATE OR REPLACE TRIGGER crewTrig1
BEFORE INSERT OR UPDATE on crewassignments
BEGIN
DECLARE @commission number(10)
IF(@commission>500 OR @commission<150)
THEN
RAISE_APPLICATION_ERROR(-20001,'Commission out of range');
END IF;
END crewTrig1;
/

Warning: Trigger created with compilation errors.

I dont know why I keep getting this error, If anyone could help I would greatly appreciate it. THANKS

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2009-11-19 : 11:13:27

Are you using SQL Server?

If you are, did you follow the link I showed?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-11-19 : 11:22:17

quote:
Originally posted by prakum

hello tkizer

in this blog "http://weblogs.sqlteam.com/tarad/archive/2004/09/14/2077.aspx" that you mentioned has below solution but i have a question in this.....

Q:Where is t1.somecolumn refers to.....bec you not created any alias name as T1.????
Explain this for me.....

CREATE TRIGGER trg_Table1
ON Table1
FOR UPDATE
AS

UPDATE t2
SET SomeColumn = t1.SomeColumn
FROM Table2 t2
INNER JOIN inserted i
ON t2.Table1_ID = i.Table1_ID

It's obviously a typo. It should be i.SomeColumn.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

prakum
Starting Member

16 Posts

Posted - 2009-11-19 : 22:41:31

ya thanks for your valuable info.....

Praveen Kumar

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources