Trigger Trouble - 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 2000 Forums

Transact-SQL (2000)

Trigger Trouble

Author

Topic

nomadsoul
Yak Posting Veteran

89 Posts

Posted - 2006-11-16 : 17:41:13

I would be grateful to anyone who could analyze this trigger I made (my first one) and tell me what I did wrong ( the error msg is below it). It always says its at the begin tran. I've tried begin transaction too. And should I put a rowcount procedure somewhere? If so where?

CREATE TRIGGER forInsertUpdate
ON ContractCopy
FOR INSERT
AS
if (select count(*)
from FacultyCopy,ContractCopy
where ContractCopy.ContractNo = FacultyCopy.FacNo)

begin
rollback tran
(Raiserror "This transaction will create anomalities.")
end

else
(RAISERROR 'Insert Complete.')

Msg 4145, Level 15, State 1, Procedure forInsertUpdate, Line 9
An expression of non-boolean type specified in a context where a condition is expected, near 'begin'.
Msg 156, Level 15, State 1, Procedure forInsertUpdate, Line 11
Incorrect syntax near the keyword 'Raiserror'.

It is better to be prepared and not have an opportunity than to not be prepared and have an opportunity

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2006-11-16 : 17:55:48

Like the error says, you need a boolean value in an IF expression - (select count(*)...) gives you an integer, so you have to compare that integer to something to get a boolean (true or false) or use EXISTS. Also don't use RAISERROR to indicate success. Something like this

CREATE TRIGGER forInsertUpdate 
ON ContractCopy
FOR INSERT 
AS 
if EXISTS (select * 
from FacultyCopy,ContractCopy
where ContractCopy.ContractNo = FacultyCopy.FacNo) 

begin
rollback tran
(Raiserror "This transaction will create anomalities.") 
end

I don't know what you're trying to do in the trigger though, you're cancelling the transaction every time if there are rows that match that query? You're not checking the data that was inserted - that's available in the inserted table that is available in an insert trigger.

I don't know whether or not you should put a rowcount because you didn't say what the trigger is for?

nomadsoul
Yak Posting Veteran

89 Posts

Posted - 2006-11-16 : 18:03:09

Im real weak on my trigger knowledge but I wanted to check for unknown values by using the join so that if there was a null value in the ContractCopy table. (at least I think that's what I'm trying to do)
Thanks for your sample. I am going to experiment with it in other places.

It is better to be prepared and not have an opportunity than to not be prepared and have an opportunity

nomadsoul
Yak Posting Veteran

89 Posts

Posted - 2006-11-16 : 18:06:33

Let me correct my last reply that:
"if there was a null value in the ContractCopy table, values would not be inserted."

It is better to be prepared and not have an opportunity than to not be prepared and have an opportunity

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2006-11-16 : 18:09:54

If you don't want null values in a column, then declare it as not null in your table definition, don't do it with a trigger. In fact I'd recommend that you forget about triggers until you find something that you specifically cannot do without a trigger because all of the initial standard table integrity can be applied with data types, null/not null, identity, and constraints.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources