SQLSERVER¼òµ¥´¥·¢Æ÷
INSERT´¥·¢Æ÷
INSERT¼°UPDATE´¥·¢Æ÷¾³£ÓÃÓÚ¼ì²â´¥·¢Æ÷Ëù¼à¿Ø±íµÄÁм°ÆäÊý¾ÝÊÇ·ñ·ûºÏËù¶¨ÒåµÄ¹æÔò¡£ËüÃÇ¿ÉÒÔÔÚÊý¾ÝÊäÈë±í֮ǰ£¬¶ÔÆä½øÐÐÔÚ¶¨ÒåÒýÓÃÍêÕûÐÔʱÎÞ·¨Íê³ÉµÄÔ¼Êø¼ìÑé¡£
ÏÂÃæÒÔѧÉúÊý¾Ý¿âstudentΪÀýÀ´½éÉÜINSERT´¥·¢Æ÷µÄʹÓ᣸ÃÊý¾Ý¿â°üÀ¨Èý¸ö±í£¬·Ö±ðÊÇÃèÊöѧÉúÇé¿öµÄ“ѧÉúµµ°¸”±í¡¢ÃèÊöѧÉú³É¼¨µÄ“ѧÉú³É¼¨”±ístudentºÍ¡£ÃèÊö·Ö×éÇé¿öµÄ“·Ö×éÇé¿ö”±ígro¡£
create table student (id numeric(1,0),name varchar(10),sex char(4),class varchar(10),gro numeric(1,0) )
create table gro (class varchar(10),gro numeric(1,0) ,num tinyint)
ΪÉÏÃæµÄ“ѧÉúµµ°¸” ±í´´½¨Ò»¸öINSERT´¥·¢Æ÷instrg£¬Æä×÷ÓÃÊÇÿÐÂÔöÒ»ÃûѧÉú¶øÐèÏò“ѧÉúµµ°¸”±íÖвåÈëÐÂÐÐʱ£¬ÔÚ“·Ö×éÇé¿ö”±íÖн«ÆäËùÔÚС×éµÄÈËÊý×Ô¶¯Ôö¼Ó1¡£
use mlh
Create Trigger instrg ON [dbo].[student]
FOR Insert
AS
declare @°à¼¶ varchar(50) ,@С×é varchar(50),@ÈËÊý tinyint
select @°à¼¶ = inserted.class,@С×é = inserted.gro from inserted
if exists(select num from gro where @°à¼¶ = gro.class and @С×é= gro.gro)
begin --bg1
select @ÈËÊý = num from gro where @°à¼¶= gro.class and @С×é = gro.gro
set @ÈËÊý = @ÈËÊý + 1
update gro set num = @ÈËÊý where @°à¼¶ = gro.class and @С×é = gro.gro
end --bg1
else
begin --bg2
insert gro values(@°à¼¶,@С×é,1)
end --bg2
UPDATE ´¥·¢Æ÷
Create Trigger stup On [dbo].[student]
FOR update
AS
Declare @°à¼¶ varchar(50) , @С×é numeric(1,0),@ÈËÊý tinyint
select @°à¼¶ = inserted.class ,@С×é= inserted.gro from inserted
if exists(select * from gro where @°à¼¶ = gro.class and @С×é = gro.gro)
begin
update gro set gro.num = gro.num + 1 where @°à¼¶ = gro.class and @С×é = gro.gro
end
else
begin
insert into gro values(@°à¼¶,@С×é,1)
end
select @°à¼¶ = deleted.class ,@С×é= deleted.gro from deleted
select @ÈËÊý = gro.num from gro where @°à¼¶ = gro.class and @С×é = gro.gro
if @ÈËÊý > 1
begin
&
Ïà¹ØÎĵµ£º
ÔÚÊý¾Ý¿â±à³ÌÖУ¬³£»áÓöµ½Òª°ÑÊý¾Ý¿â±íÐÅÏ¢µ¼ÈëExcelÖУ¬ ÓÐʱÔòÊÇ°ÑExcelÄÚÈݵ¼ÈëÊý¾Ý¿âÖС£ÔÚÕâÀ½«½éÉÜÒ»ÖֱȽϷ½±ã¿ì½ÝµÄ·½Ê½£¬Ò²ÊDZȽÏÆÕ±éµÄ¡£Æäʵ£¬Õâ·½·¨Äã²¢²»Ä°Éú¡£ÔÀíºÜ¼òµ¥£¬°ÑÊý¾Ý¿â±í»òExcelÄÚÈݶÁÈ¡µ½datasetÀàÐ͵ıäÁ¿ÖУ¬ÔÙÖð ......
Mysql£¬SqlServer£¬OracleÖ÷¼ü×Ô¶¯Ôö³¤µÄÉèÖÃ
1¡¢°ÑÖ÷¼ü¶¨ÒåΪ×Ô¶¯Ôö³¤±êʶ·ûÀàÐÍ
ÔÚmysqlÖУ¬Èç¹û°Ñ±íµÄÖ÷¼üÉèΪauto_incrementÀàÐÍ£¬Êý¾Ý¿â¾Í»á×Ô¶¯ÎªÖ÷¼ü¸³Öµ¡£ÀýÈ磺
create table customers(id int auto_increment primary key not null, name varchar(15));
insert into customers(name) values("name1"),("nam ......
Author URL:http://www.cnblogs.com/xbf321/archive/2008/11/02/1325067.html
Microsoft URL:http://technet.microsoft.com/zh-cn/library/ms188001.aspx
ÕªÒª
1,EXECµÄʹÓÃ
2£¬sp_executesqlµÄʹÓÃ
MSSQLΪÎÒÃÇÌṩÁËÁ½ÖÖ¶¯Ì¬Ö´ÐÐSQLÓï¾äµÄÃüÁ·Ö±ðÊÇEXECºÍsp_executesql;ͨ³ ......
ÔÚʹÓÃÊý¾Ý¿âµÄ¹ý³ÌÖУ¬¾³£»áÓöµ½Êý¾Ý¿âǨÒÆ»òÕßÊý¾ÝǨÒƵÄÎÊÌ⣬»òÕßÓÐͻȻµÄÊý¾Ý¿âË𻵣¬ÕâʱÐèÒª´ÓÊý¾Ý¿âµÄ±¸·ÝÖÐÖ±½Ó»Ö¸´¡£µ«ÊÇ£¬´Ëʱ»á³öÏÖÎÊÌ⣬ÕâÀï˵Ã÷¼¸ÖÖ³£¼ûÎÊÌâµÄ½â¾ö·½·¨¡£
¹ÂÁ¢Óû§µÄÎÊÌâ
±ÈÈ磬ÒÔÇ°µÄÊý¾Ý¿âµÄºÜ¶à ......