making record read-only

Hi,

i want to make a certain records read-only using stored procedure. something like:

Update dbo.Articles
SET record = "read-only"
WHERE
ArticleID = @.ArticleID

(you know what i mean)

the reason i want to do that is that im publishing articles and i want to lock the articles once they're published so no body can change them unless they're unpublished.

of course i aslo want to know how to make a record read-write again.

ThanxAnswer: you can not. Row level security is not part of SQL.

What yo ucould do is a Trigger that blos and rolld back the transaction when a row has a specific value (read only flag).

But then - why the heck dont you handle this in the frontend application? Just dont let the user delete.|||well i wanted i did that in the front end.. just wanted to do it at the backend to ensure data integrity.

Thank you|||::just wanted to do it at the backend to ensure data integrity.

Sadly, this is nonsense, as this does not touch the issue of data integrity. Relational integrity has nothing to do with being able to delete something or not.

Now, in the rare case this needs to be blocked, a trigger that blows the transaction is about the only way to go :-)sql

make procedure to check balance > or < totalcost

helo all...,

i have create procedure can decreasetotalcost from order table(database:games.dbo) withbalance in bill table(database:bank.dbo). my 2 database in same server is name "boy"

i have 2 database like: bank.dbo and games.dbo

in games.dbo, have a table name is order(user_id,no_order,date,totalcost)

in bank.dbo, have a table name like is bill(no_bill,balance)

this is a list of bill table

no_bill balance

111222 200$

222444 10$

this is a list of order table

user_id no_order date totalcost

a 1 1/1/07 50$

when customer insert no_bill(111222) in page and click a button, then bill table became

no_bill balance

111222 150$

222444 10$

when customer insert no_bill(222444) in page and click a button, then message "sorry, your balance is not enough"

mystore procedure like:

ALTER PROCEDURE [dbo].[pay]
(
@.no_bill AS INT,
@.no_order AS int,
@.totalcost AS money
)
AS
BEGIN
BEGIN TRANSACTION

DECLARE @.balanc AS money

SET @.balanc= (SELECT [balance] FROM Bank.dbo.bill WHERE [no_bill] = @.no_bill)

UPDATE [bank.dbo.bill]
SET
[balance] = @.balanc - @.totalcost
WHERE
[no_bill] = @.no_bill

COMMIT TRANSACTION
END

it can decrease money in bank, but i want it ceck money if balance > totalcost, so balance-totalcost,

if balance<totalcost,so error message"sorry, your balance not enough"

is it can make in procedure?

thx...

Try this:

ALTER PROCEDURE [bank].[dbo].[pay]( @.no_billINT, @.no_orderint, @.totalcostmoney, @.messagevarchar(100)-- make it output parameter in your stored procedure)ASBEGIN TRANSACTION DECLARE @.balancAS moneyselect @.balance = balancefrom bank.dbo.billwhere no_bill = @.no_billselect @.totalcost = totalcostfrom games.dbo.totalcostwhere no_order = @.no_orderif (@.balance > @.totalcost)beginset @.balance = @.balance - @.totalcostUPDATE bank.dbo.billSET [balance] = @.balanceWHERE [no_bill] = @.no_bill-- set @.message = 'your have enough balance'endelsebeginset @.message ='sorry, your balance not enough'endCOMMIT TRANSACTIONset nocount off

Good luck.

|||

thx...

ur code is not display message. when i execute ur store procedure, it display:

type direction name value

int in no_bill we insert to this,ex:110

int in no_order we insert to this,ex:2

money in totalcost we insert to this, ex 100$

char in message ??? if i not insert to this, my error :Procedure or Function 'pay' expects parameter '@.message', which was not supplied.

so it must to insert it, but my purpose is display message automatic. how can i change direction to be output?

can u add output code to stroreprocedure?

pls..,thx...

Make C#

Can somebody help me convert this SQL2000 Function TO C# Function please

CREATE Function dbo.CalculateNextRentDate ( @.Rent_Payment_Date datetime, @.Frequency varchar(50) , @.Number int)

RETURNS DATETIME

AS
begin

DECLARE @.NextPaymentDate Datetime
SET @.Number = @.Number - 1
.
IF @.Frequency = 'month'
IF @.rent_payment_date = dateadd(month, datediff(month, 0+@.Number, @.rent_payment_date) + 1, -1)
BEGIN
SET @.NextPaymentDate = dateadd(month, datediff(month, 0, @.rent_payment_date) + 2, -1)
END
ELSE
BEGIN
SET @.NextPaymentDate = dateadd(month, 1+@.Number, @.rent_payment_date)
END

return @.NextPaymentDate
end

I can't understand why in the T-SQL function it first checks whether the @.Rent_Payment_Date is the last day of the month, so I just remove the check and here is the code:

public DateTime CalculateNextRentDate(DateTime Rent_Payment_Date, string Frequency, int Number)
{
DateTime NextPaymentDate;
NextPaymentDate = Rent_Payment_Date;
Number--;
if (Frequency == "month")
{
NextPaymentDate = Rent_Payment_Date.AddMonths(Number);
}
return NextPaymentDate;
}