SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


force input of another field when putting a value of 1 in field


force input of another field when putting a value of 1 in field

Author
Message
kathleen.ermatinger
kathleen.ermatinger
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
Points: 4 Visits: 9
I have a field to indicate a delete which will be a text char of "1" in a field called deleterecord. Deleterecord has a default value of 0. This field is a text field char (1) and can only have the value of 0 or 1 with a FK lookup on another table. When the user changes the 0 to a 1, I want to force them to add the reason for the delete in another field called deletereason. I was thinking a trigger would work but being new to sql, not sure how to set this up. The table is called masterunit.
Lynn Pettis
Lynn Pettis
SSC Guru
SSC Guru (96K reputation)SSC Guru (96K reputation)SSC Guru (96K reputation)SSC Guru (96K reputation)SSC Guru (96K reputation)SSC Guru (96K reputation)SSC Guru (96K reputation)SSC Guru (96K reputation)

Group: General Forum Members
Points: 96827 Visits: 38988
I am assuming users insert/update/delete records using an application, not by directly writing SQL statements. If so, this is something that should be handled in the application.

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
kathleen.ermatinger
kathleen.ermatinger
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
Points: 4 Visits: 9
all updates are being done through access which is connected to the sql db through odbc connection.
eklavu
eklavu
SSC Veteran
SSC Veteran (224 reputation)SSC Veteran (224 reputation)SSC Veteran (224 reputation)SSC Veteran (224 reputation)SSC Veteran (224 reputation)SSC Veteran (224 reputation)SSC Veteran (224 reputation)SSC Veteran (224 reputation)

Group: General Forum Members
Points: 224 Visits: 173
There are diffrent approaches in sql to do this. Triggers, output clause or the the parameter value can be determined if it will save to your deleted messages tables.

If you are using access, here is the sample code in sql with minimal conversion to access code. Just convert it. :-)

if action = 1
begin
update masterunit
set deletedrecord = 1

insert into deletereason (reason)
values ("your reason")
end
dwain.c
dwain.c
SSCoach
SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)

Group: General Forum Members
Points: 18189 Visits: 6431
You can avoid inadvertant DELETEs or UPDATEs that mark the record as deleted (without a reason) like this:


CREATE TABLE MyTable
(MyPK INT PRIMARY KEY
,othercolumn VARCHAR(10)
,deleterecord CHAR(1) DEFAULT('0') CHECK (deleterecord IN ('0', '1'))
,deletereason VARCHAR(200) DEFAULT(NULL))
GO
CREATE TRIGGER MyTable_delete ON MyTable
INSTEAD OF UPDATE, DELETE
AS BEGIN
UPDATE t
SET deleterecord = i.deleterecord
,deletereason = i.deletereason
,othercolumn = i.othercolumn
FROM MyTable t
INNER JOIN INSERTED i ON t.MyPK = i.MyPK
WHERE (i.deleterecord = '1' AND i.deletereason IS NOT NULL) or i.deleterecord = '0'
END
GO

INSERT INTO MyTable (MyPK)
SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3

SELECT * FROM MyTable

DELETE FROM MyTable WHERE MyPK = 1

UPDATE MyTable SET othercolumn = 'EXAMPLE' WHERE MyPK = 1
UPDATE MyTable SET deleterecord = '1' WHERE MyPK = 2
UPDATE MyTable SET deleterecord = '1', deletereason = 'MY CHOICE' WHERE MyPK = 3

SELECT * FROM MyTable

DROP TRIGGER MyTable_delete
DROP TABLE MyTable




However you will have to check in your application that a delete action (mark deleted) includes a reason because it is not easy in a trigger to return an error back to the client telling you when the mark deleted action doesn't include a reason.

I suppose you could raise an error and then check for it in the client but I've never tried that.

Also, you'll need to add additional columns to the SET within the UPDATE of the trigger, to also apply any other columns' new values (as obtained from the INSERTED psuedotable) as I have done with the othercolumn column.

I was also a bit weirded out by your suggestion that your assigning a FK check constraint to the deleterecord column when all you need is a simple CHECK constraint like the one I did above.


My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.


Need to UNPIVOT? Why not CROSS APPLY VALUES instead?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
My temporal SQL musings: Calendar Tables, an Easter SQL, Time Slots and Self-maintaining, Contiguous Effective Dates in Temporal Tables
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search