Hi Friends,
In my project two tables one is Head and another is Detail.
Head table columns are - HeadId,Name,Address
Detail table columns are - DetailId (primary key), description, rate, HeadId(foreign key)
Sometimes what happend,Record is inserted only in Head table but not inserted in Detail table.I want to use transaction in stored procedure for insert record in both tables at a time. and one more thing is I want to insert more than one record in Detail table and in Head table insert only one record.
e.g. Head - 101,ABC,XYZ
Detail - 1,aaa,10,101
2,bbb,20,101
3,ccc,30,101
please give me solution for this scenario
Loading
keyurPosted Feb 9, 2015, 4:47 AM
USE pubs
DECLARE @intErrorCode INT
BEGIN TRAN
// write here your insert statments
UPDATE Authors
SET Phone = '415 354-9866'
WHERE au_id = '724-80-9391'
SELECT @intErrorCode = @@ERROR
IF (@intErrorCode <> 0) GOTO PROBLEM
UPDATE Publishers
SET city = 'Calcutta', country = 'India'
WHERE pub_id = '9999'
SELECT @intErrorCode = @@ERROR
IF (@intErrorCode <> 0) GOTO PROBLEM
COMMIT TRAN
PROBLEM:
IF (@intErrorCode <> 0) BEGIN
PRINT 'Unexpected error occurred!'
ROLLBACK TRAN
END
Hope this help