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.
| Author |
Topic |
|
steelkilt
Constraint Violating Yak Guru
255 Posts |
 Posted - 2002-11-05 : 13:23:15
|
| Can someone tell me why SQL delivers "Error 207" invalid fieldnames, when I try to create this trigger? My fieldnames are valid but the fields listed after COALESCE are tagged by SQL as invalid?? ======================================================= CREATE TRIGGER maintable_ratable ON maintable FOR INSERT, UPDATE AS IF COALESCE (valid_fieldname1, valid_fieldname2, valid_fieldname3) IS NOT NULL BEGIN INSERT INTO valid_fieldname_4 VALUES ('R') END ELSE INSERT INTO valid_fieldname4 VALUES ('NR') |
|
|
LarsG
Constraint Violating Yak Guru
284 Posts |
Posted - 2002-11-05 : 13:34:56
|
| if not exists(select * from inserted where valid_fieldname1 is nullor valid_fieldname2 is nullor valid_fieldname3 IS NULL) BEGIN INSERT INTO valid_fieldname_4 VALUES ('R') END ELSE INSERT INTO valid_fieldname4 VALUES ('NR') The changed data can only be access by using the pseudo tables insert/deleted |
 |
|
|
steelkilt
Constraint Violating Yak Guru
255 Posts |
Posted - 2002-11-05 : 14:42:20
|
| thanks for the code. almost there. now SQL tells me my valid_fieldname4 is "invalid", so the trigger is launching, but appears to be bombing at the point of data insertion.thx |
|
|
|
LarsG
Constraint Violating Yak Guru
284 Posts |
Posted - 2002-11-05 : 14:48:31
|
| Restart.What are you trying to accomplish? What is valid_fieldname_4? |
|
|
|
steelkilt
Constraint Violating Yak Guru
255 Posts |
Posted - 2002-11-05 : 15:40:04
|
| Sorry if I have been less than clear. What I'm trying to do is check a set of fields for NULLS. If the selected set of fields are all NULLs, then my item is "not ratable" and therefore I'd like to enter 'NR' in my "status" field (AKA valid_fieldname4). If there is data in at least on of the selected fields, this means the item is "ratable" and thus an 'R' should automatically be entered by the trigger procedure in the "status" field. This is the reason I presented the original piece of code using COALESCE to check for a value in any of the selected fields.thanks. |
|
|
|
LarsG
Constraint Violating Yak Guru
284 Posts |
Posted - 2002-11-05 : 15:57:08
|
| CREATE TRIGGER maintable_ratable ON maintable FOR INSERT, UPDATE AS update maintable set valid_fieldname_4 = case when i.valid_fieldname1 is null and i.valid_fieldname2 is null and i.valid_fieldname3 is null then 'NR' else 'R' end from maintable m join inserted on m.id = i.idI don't see any reason for doing this, though. This type of virtual columns may just as well be created when doing the select. For instance, you could create a view for this contingency. |
|
|
|
|
|
|
|
|
can't CREATE TRIGGER, "invalid fieldnames"??