quote:

---

if((select A from addBook)='abcd' where B='fs')
print('ok')
else
print('not ok')

---

quote:

---

Originally posted by upani

Thanks visakh16!
But i still encounter when i write a simple statement like that
I have two table Book and borrow


Book has two columns are: code((primary key) and status
borrow has one column code_book: (references to code of book)

I want to create a trigger that if i insert a recode into borrow, it will check if status of Book is 'exist' or 'not exist'. If it doesn't exist, rollback transaction
But when i test, transaction still executes although the status responsibles the code is 'not exits'. I don't know why. Please show me!
Here's my code

create table Book(
code varchar(9) primary key,
status varchar(15));

create table borrow(
code_book varchar(9) references Book(code)
)

create trigger check_for_borrow
on borrow for insert as
declare @val varchar
select @val = status from Book Where
(Select count(*) from Book,inserted
where
Book.code=inserted.code_book)=1
if(@val='not exist')
begin
print('This book doesnt exist')
rollback transaction
end
else
print('ok')

---

create trigger check_for_borrow
on borrow 
instead of insert as
begin
if exists (select 1 from inserted i
           join book b
           on b.code=i.code_book
           where b.status='exist')
begin
insert into borrow
select i.code_book
from inserted i
join book b
on b.code=i.code_book
where b.status='exist'
end
end

quote:

---

Originally posted by visakh16

why not make it a instead of trigger then?

create trigger check_for_borrow
on borrow 
instead of insert as
begin
if exists (select 1 from inserted i
           join book b
           on b.code=i.code_book
           where b.status='exist')
begin
insert into borrow
select i.code_book
from inserted i
join book b
on b.code=i.code_book
where b.status='exist'
end
end

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

---

quote:

---

Originally posted by upani

Hi!
When i create borrow table as the way visakha16 tell me:

create table borrow(
code_book varchar(9) references Book(code)ON DELETE CASCADE
)

And an delete a recode from Book:

delete from Book where code='A225'
Then it does'n work, the result is:
(0 row(s) affected)
Msg 208, Level 16, State 1, Procedure checkdel, Line 3
Invalid object name 'bororow'.

Please tell me why?

---