use tempdb gocreate table a2 ( iid int identity(1,1), [name] nvarchar(100) ) create table a11 ( iid int ) ---事物 begin tran insert into a2([name]) values('renfengquan') insert into a11(iid) values(ident_current('a2')) commit tran --触发器 create trigger Insert_A on a2 for insert as insert into A11 values(@@Identity)
insert 之后执行
declare @id int
select @id=max(id) from table
insert table b(id)
values(@id)
事物的 话,两条SQL语句放到一个 事物中就行,逻辑的 话 大概如下:
if(插入A 失败)
{
跳出
}
else
{
if(插入B失败)
{ 删除A}
}
myConnection.Open();SqlTransaction myTrans = myConnection.BeginTransaction(); //使用New新生成一个事务
SqlCommand myCommand = new SqlCommand();
myCommand.Transaction = myTrans;try
{
myCommand.CommandText = "insert into CRM_Staff(ids)values('1');select @@IDENTITY";
object obj = myCommand.ExecuteScalar(); //获取自增ID
if (obj == null)
{
myCommand.CommandText = "insert into CRM_Staff2(ids)values(obj.tostring() )";
obj = myCommand.ExecuteScalar();
myTrans.Commit(); //提交事务
}
}
catch(Exception e)
{
myTrans.Rollback();
Console.WriteLine(e.ToString());
Console.WriteLine("Sorry, Record can not be updated.");
}
finally
{
myConnection.Close();
}
if (obj != null)打错
代码5楼已经给了,你改动下就可以了。
--大体可以这样
create trigger tri_insert on A for insert
as
if @@rowcount<>0
insert into B(id) select id from inserted
protected void btnOK_Click(object sender, EventArgs e)
{ //定义一个SqlData的新实例对象
SqlData da = new SqlData();
//调用公共类的ExceRead方法,返回SqlDataReader类型的数据
SqlDataReader read = da.ExceRead("select * from tbUser where UserName='" + this.txtName.Text + "'");
//读取数据库中的数据
read.Read();
read.Close();
//定义一个字符串,插入相关用户信息
string P_str_Com = "insert into tbUser(UserName,PassWord,Email,QQ,Sex,RegTime,IP)"
+ " values('" + this.txtName.Text + "','" + this.txtPwd.Text + "','" + this.txtEmail.Text + "','" +this.txtQQ.Text+ "','" + this.ddlSex.SelectedValue + "','" + DateTime.Now.ToString() + "','" + Page.Request.UserHostAddress + "')";
//调用公共类中的ExceSQL方法,执行insert语句
bool add = da.ExceSQL(P_str_Com);
if (add)
{
string ID;//字符串ID
SqlConnection mycon = new SqlConnection(ConfigurationManager.AppSettings["conStr"]);
mycon.Open();
DataSet mydataset = new DataSet();
SqlDataAdapter mydataadapter = new SqlDataAdapter("select * from tbUser where UserName='" + Session["UserName"] + "'", mycon);
mydataadapter.Fill(mydataset, "tbUser");
DataRowView rowview = mydataset.Tables["tbUser"].DefaultView[0];
ID = rowview["UserID"].ToString();//用ID值等于A表ID值
//将A表ID插入B表
string P_str_Com = "Insert into tb_Message(FriendName,QQ,Email,UserID)"
+" values ('"+this.txtName.Text+"','"+this.txtQQ.Text+"','"+this.txtEmail.Text+"','"+ID+"')";
Response.Write("<script language=javascript>alert('恭喜您!注册成功!');location='../Index.aspx'</script>");
}
else
{
Response.Write("<script language=javascript>alert('对不起!您注册失败!');location='javascript:history.go(-1)'</script>");
}
}
}
gocreate table a2
(
iid int identity(1,1),
[name] nvarchar(100)
)
create table a11
(
iid int
)
---事物
begin tran
insert into a2([name]) values('renfengquan')
insert into a11(iid) values(ident_current('a2'))
commit tran
--触发器
create trigger Insert_A on a2
for insert
as
insert into A11 values(@@Identity)
错误信息:ExecuteScalar: Connection 属性尚未初始化。