CREATE TRIGGER rece_trigger ON [rece_message]
FOR INSERT
AS
declare @message char(50),@answer_message char(200)
select @message=message from rece_message where id=(select max(id) from rece_message)
if substring(@message,1,2)='cp'
begin
if (len(@message)>=10) begin select @answer_message='请输入正确的格式,正确的格式为:CP20193'end
else
begin
select @answer_message='车主姓名'+car_owner_name+'证件号码'+car_owner_no+'车辆类型'+car_type from new_point
where car_no=substring(@message,3,10)
select @answer_message='对不起,您查询的记录不存在' where not exists (select * from new_point
where car_no=substring(@message,3,10))
end
end else
if substring(@message,1,2)='zj'
begin
if (len(@message)>20) begin select @answer_message='请输入正确的格式,正确的格式为:ZJ31'end
else
begin
select @answer_message='车主姓名'+car_owner_name+'车牌号'+car_no+'车辆类型'+car_type from new_point
where car_owner_no=substring(@message,3,18)
select @answer_message='对不起,您查询的记录不存在' where not exists (select * from new_point
where car_owner_no=substring(@message,3,18))
end
end else
begin
select @answer_message='请输入正确的格式'
end
update rece_message set answer_message=@answer_message where id=(select max(id) from rece_message)
FOR INSERT
AS
declare @message char(50),@answer_message char(200)
select @message=message from rece_message where id=(select max(id) from rece_message)
if substring(@message,1,2)='cp'
begin
if (len(@message)>=10) begin select @answer_message='请输入正确的格式,正确的格式为:CP20193'end
else
begin
select @answer_message='车主姓名'+car_owner_name+'证件号码'+car_owner_no+'车辆类型'+car_type from new_point
where car_no=substring(@message,3,10)
select @answer_message='对不起,您查询的记录不存在' where not exists (select * from new_point
where car_no=substring(@message,3,10))
end
end else
if substring(@message,1,2)='zj'
begin
if (len(@message)>20) begin select @answer_message='请输入正确的格式,正确的格式为:ZJ31'end
else
begin
select @answer_message='车主姓名'+car_owner_name+'车牌号'+car_no+'车辆类型'+car_type from new_point
where car_owner_no=substring(@message,3,18)
select @answer_message='对不起,您查询的记录不存在' where not exists (select * from new_point
where car_owner_no=substring(@message,3,18))
end
end else
begin
select @answer_message='请输入正确的格式'
end
update rece_message set answer_message=@answer_message where id=(select max(id) from rece_message)
FOR INSERT
AS
declare @message char(50),@answer_message char(200)
select @message=message from rece_message where id=(select max(id) from rece_message)
if substring(@message,1,2)='cp'
begin
if (len(@message)>=10) begin select @answer_message='请输入正确的格式,正确的格式为:CP20193'end
else
begin
select @answer_message='车主姓名'+car_owner_name+'证件号码'+car_owner_no+'车辆类型'+car_type from new_point
where car_no=substring(@message,3,10)
select @answer_message='对不起,您查询的记录不存在' where not exists (select * from new_point
where car_no=substring(@message,3,10))
end
end else
if substring(@message,1,2)='zj'
begin
if (len(@message)>20) begin select @answer_message='请输入正确的格式,正确的格式为:ZJ31'end
else
begin
select @answer_message='车主姓名'+car_owner_name+'车牌号'+car_no+'车辆类型'+car_type from new_point
where car_owner_no=substring(@message,3,18)
select @answer_message='对不起,您查询的记录不存在' where not exists (select * from new_point
where car_owner_no=substring(@message,3,18))
end
end else
begin
select @answer_message='请输入正确的格式'
end
update rece_message set answer_message=@answer_message where id=(select max(id) from rece_message)
/*****触发器KeyCode_Modi*****/
CREATE TRIGGER [KeyCode_Modi] ON [T_KeyCode]
FOR INSERT, UPDATE, DELETE
AS
Update T_DownNote
Set KeyCode=1
for insert,update,delete
as
update TDOwnNote
set keyCode=1
FOR DELETE
AS
declare @PartCode char(10),@Number float
declare delete_cursor cursor for select PartCode,InNumber from deleted
open delete_cursor
fetch next from delete_cursor into @PartCode,@Number
while @@fetch_status=0
begin
update part set Currentstorage=CurrentStorage-@Number where
PartCode=@PartCode
fetch next from delete_cursor into @PartCode,@Number
end
close delete_cursor
deallocate delete_cursorCREATE TRIGGER [tri_update_inrec] ON dbo.InRecord
FOR UPDATE
AS
Declare @NewNumber float,@OldNumber float,@OldPartCode int,@NewPartCode int
if update(innumber) or update(PartCode)
begin
declare update_cursor cursor for select i.PartCode as NewPartCode,d.PartCode as OldPartCode,i.innumber as newnumber,d.innumber as OldNumber from inserted i ,deleted d where i.InRecordID=d.InRecordID
open update_cursor
fetch next from update_cursor into @NewPartCode,@OldPartCode,@NewNumber,@OldNumber
while @@fetch_status=0
begin
update part set CurrentStorage=CurrentStorage+@NewNumber where PartCode=@NewPartCode
update part set CurrentStorage=CurrentStorage-@OldNumber where PartCode=@OldPartCode
fetch next from update_cursor into @NewPartCode,@OldPartCode,@NewNumber,@OldNumber
end
close update_cursor
deallocate update_cursor
end
CREATE TRIGGER [tri_insert_inrec] ON dbo.InRecord
FOR INSERT
AS
if @@rowcount>0
begin
Declare @InNumber float,@PartCode int,@InPrice numeric(12,2)
declare insert_cursor cursor for select PartCode,InNumber,InPrice from inserted
open insert_cursor
fetch next from insert_cursor into @PartCode,@InNumber,@InPrice
while @@fetch_status=0
begin
update part set part.CurrentStorage=part.CurrentStorage+@InNumber,LastInPrice=@InPrice
where part.PartCode=@PartCode
fetch next from insert_cursor into @PartCode,@InNumber,@InPrice
end
close insert_cursor
deallocate insert_cursor
end