March 29, 2012 at 12:46 am
I have below tables,
college_cutoffmaster
College_CutoffCodeTransition
College_YearCutoffTransition
in which we do insert update and delete records through software into database.
Problem is that whatever entry i am doing in above table i need migrate the data in another 3 tables as below,
College_AieeecutoffMaster
college_AieeecollegecodeTransition
College_AieeeYearcutoffTransition
parameters, and tables size are same only difference is in parameter-name are differ.
Is there any way if i do entry,updation and deletion in table then changes will be reflect in another tables.. condition is that both table records should be same with ID.
Please Help.
Thanks & Regards,
Pallavi
March 29, 2012 at 1:07 am
Hi Pallavi,
Did you consider creating after triggers on your first set of tables? You can capture the changes through the 'magic tables' -Inserted and Deleted.
March 29, 2012 at 2:31 am
Try creating after trigger on the first set of tables and writedown the logic to perform DML operations on the second set with the magic tables Inserted and Deleted it may work.
March 29, 2012 at 7:25 am
I implemented trigger on one table,
CREATE TABLE [dbo].[Employee_Test](
[Emp_ID] [int] IDENTITY(1,1) NOT NULL,
[Emp_name] [varchar](100) NULL,
[Emp_Sal] [decimal](10, 2) NULL,
[Id] [numeric](18, 0) NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Employee_Test_Audit](
[Emp_ID] [int] NULL,
[Emp_name] [varchar](100) NULL,
[Emp_Sal] [decimal](10, 2) NULL,
[Id] [numeric](18, 0) NULL,
[Audit_Action] [varchar](100) NULL,
[Audit_Timestamp] [datetime] NULL
) ON [PRIMARY]
GO
INSERT INTO Employee_Test VALUES ('Anees',1000,1);
INSERT INTO Employee_Test VALUES ('Rick',1200,2);
INSERT INTO Employee_Test VALUES ('John',1100,1);
INSERT INTO Employee_Test VALUES ('Stephen',1300,3);
INSERT INTO Employee_Test VALUES ('Maria',1400,1);
INSERT INTO Employee_Test VALUES ('pallavi',1400,4);
condition is that I wanted to set a trigger on thoes values where Id=1
I written belo trigger,
CREATE TRIGGER trgAfterInsert ON [dbo].[Employee_Test]
FOR INSERT
AS
declare @empid int;
declare @empname varchar(100);
declare @empsal decimal(10,2);
declare @id numeric(18,0);
declare @audit_action varchar(100);
select @empid=i.Emp_ID from inserted i;
select @empname=i.Emp_Name from inserted i;
select @empsal=i.Emp_Sal from inserted i;
select @id=i.id from inserted i;
set @audit_action='Inserted Record -- After Insert Trigger.';
insert into Employee_Test_Audit
(Emp_ID,Emp_Name,Emp_Sal,Id,Audit_Action,Audit_Timestamp)
values(@empid,@empname,@empsal,@id,@audit_action,getdate()) from Employee_Test where @id=1;
PRINT 'AFTER INSERT trigger fired.'
GO
But It's showing an error like :-Msg 156, Level 15, State 1, Procedure trgAfterInsert, Line 19
Incorrect syntax near the keyword 'from'.
is it possible to write a condition in trigger?????
Thanks & Regards,
Pallavi
March 29, 2012 at 7:38 am
Pallavi,
I cannot find any id over there do you mean emp_id.?
Can you be a bit clear on the requirement u want .?
Maddy
March 29, 2012 at 7:46 am
Hello Pallavi,
Here is ur cooked item on the plate
ALTER TRIGGER trgAfterInsert ON [dbo].[Employee_Test]
AFTER INSERT
AS
BEGIN
PRINT 'AFTER INSERT trigger fired.'
INSERT INTO Employee_Test_Audit
(Emp_ID,Emp_Name,Emp_Sal,Id,Audit_Action,Audit_Timestamp)
SELECT EMP_ID,EMP_NAME,EMP_SAL,ID,'Inserted Record -- After Insert Trigger.',GETDATE()
FROMINSERTED
WHEREID = 1
END
GO
Please do lemme know if this is not meeting your requirement.
March 29, 2012 at 7:51 am
Please see the table [dbo].[Employee_Test]
Emp_IDEmp_nameEmp_SalId
1Anees1000.001
2Rick1200.004
3John1100.002
4Stephen1300.001
5Maria1400.001
6pallavi1400.003
in that one column is exist named "Id"
so want to insert a record for specific id which i mentiond in trigger for inser query see,
insert into Employee_Test_Audit
(Emp_ID,Emp_Name,Emp_Sal,Id,Audit_Action,Audit_Timestamp)
values(@empid,@empname,@empsal,@id,@audit_action,getdate()) from Employee_Test where @id=1;
Thanks & Regards,
Pallavi
March 29, 2012 at 7:57 am
Yah It's working Now for specific id 🙂
Thank you
Thanks & Regards,
Pallavi
March 29, 2012 at 8:02 am
The trigger does not look right to me at all. It does not support multiple row inserts. Even though the hard coded value of 1 seems a bit odd you are not processing all your records on insert.
What happens if you have a multi row insert? Your trigger is coded to handle 1 and only 1 row.
Do you really only want to migrate data where the inserted value for ID = 1?
If you can give me some clear business rules I will help you code this trigger to work on a set instead of 1 row.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 29, 2012 at 8:04 am
The version that Maddy posted looks like a better solution. I apologize Maddy I did not see your response when I posted my first one.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 29, 2012 at 9:30 am
Thank You Sean Lange
Ur welcome Pallavi.
March 30, 2012 at 1:34 am
Please help me i get trapped over here in trigger again,
It's giving duplicate records
tables i have as below
First one
CREATE TABLE [dbo].[College_CutoffMaster](
[CutoffId] [numeric](18, 0) IDENTITY(1,1) NOT NULL,
[Cutoff] [numeric](18, 0) NULL,
[CollegeId] [numeric](18, 0) NOT NULL,
[DegreeId] [numeric](18, 0) NOT NULL,
[StreamId] [numeric](18, 0) NOT NULL,
[EntranceId] [numeric](18, 0) NOT NULL,
[Gender] [nchar](10) NOT NULL,
[UniversityType] [nchar](2) NULL,
[Capround] [nchar](10) NULL,
CONSTRAINT [PK_College_CutoffMaster] PRIMARY KEY CLUSTERED
(
[CutoffId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
second is,
CREATE TABLE [dbo].[College_CutoffCodeTransition](
[CutoffId] [numeric](18, 0) NULL,
[CollegeCode] [int] NULL
) ON [PRIMARY]
GO
third is
CREATE TABLE [dbo].[College_Maharashtra_BEngcutoffmaster1](
[CutoffId] [numeric](18, 0) NOT NULL,
[Cutoff] [numeric](18, 0) NULL,
[CollegeId] [numeric](18, 0) NULL,
[DegreeId] [numeric](18, 0) NULL,
[StreamId] [numeric](18, 0) NULL,
[EntranceId] [numeric](18, 0) NULL,
[Gender] [nchar](10) NULL,
[UniversityType] [nchar](10) NULL,
[Capround] [nchar](10) NULL,
[CollegeCode] [numeric](18, 0) NULL,
[Rank] [numeric](18, 0) NULL,
[Audit_Timestamp] [varchar](100) NULL,
[CreatedOn] [datetime] NULL,
[CreatedBy] [numeric](18, 0) NULL,
[EditedOn] [datetime] NULL,
[EditedBy] [numeric](18, 0) NULL,
[Version] [int] NULL
) ON [PRIMARY]
and below is trigger i have created,
CREATE trigger [dbo].[trg_After_Cutoffmaster_Insert] on
[dbo].[College_CutoffMaster]
FOR INSERT
AS
declare @CutoffId numeric(18,0);
declare @Cutoff numeric(18,0);
declare @CollegeId numeric(18,0);
declare @degreeid numeric(18,0);
declare @streamid numeric(18,0);
declare @entranceid numeric(18,0);
declare @gender nchar(10);
declare @UniversityType nchar(10);
declare @capround nchar(10);
declare @CollegeCode numeric(18,0);
declare @Rank numeric(18,0);
declare @audit_action varchar(100);
select @CutoffId=i.CutoffId from inserted i;
select @Cutoff=i.cutoff from inserted i;
select @CollegeId=i.CollegeId from inserted i;
select @degreeid=i.degreeid from inserted i;
select @streamid=i.streamid from inserted i;
select @entranceid=i.entranceid from inserted i;
select @gender=i.gender from inserted i;
select @UniversityType=i.UniversityType from inserted i;
select @capround=i.capround from inserted i;
--select @CollegeCode=i.CollegeCode from inserted i;
--select @Rank=i.Rank from inserted i;
set @audit_action='Inserted Record -- After Insert Trigger.';
insert into College_Maharashtra_BEngcutoffmaster1([CutoffId],cutoff,collegeid,degreeid,streamid,entranceid,gender,UniversityType,Capround,collegecode,Audit_Timestamp)
select a.[CutoffId],[Cutoff],[CollegeId],[DegreeId],[StreamId],[EntranceId],[Gender],[UniversityType],[Capround],
b.collegecode,'Inserted Record -- After Insert Trigger.'
from College_CutoffMaster a, College_CutoffCodeTransition b
where a.cutoffid=b.cutoffid
PRINT 'AFTER INSERT trigger fired.'
GO
problem is that the College_Maharashtra_BEngcutoffmaster1 are showing duplicate rows as record is inserted only once.
here i am inserting a record in College_Maharashtra_BEngcutoffmaster1 by joining College_CutoffMaster and College_CutoffCodeTransition tables.
How could i get out of this problem???
is it possible to use join here ???
Thanks & Regards,
Pallavi
March 30, 2012 at 2:31 am
Hello Pallavi,
Replace College_CutoffMaster in the select with INSERTED and see how it works and in the mean time I will look into the issue where you are going wrong.
And I dont think you need all the variables declaration stuff because you are nowhere using them at all and
are you sure there will be only one record in College_CutoffCodeTransition for one cuttoffid.
Thank You,
Maddy.
March 30, 2012 at 3:21 am
Yes there will be a only one cutoff if in codetransition table
I did a changes u suggested as below,
alter trigger trg_After_Cutoffmaster_Insert on
College_CutoffMaster
FOR INSERT
AS
declare @CutoffId numeric(18,0);
declare @Cutoff numeric(18,0);
declare @CollegeId numeric(18,0);
declare @degreeid numeric(18,0);
declare @streamid numeric(18,0);
declare @entranceid numeric(18,0);
declare @gender nchar(10);
declare @UniversityType nchar(10);
declare @capround nchar(10);
declare @CollegeCode numeric(18,0);
declare @Rank numeric(18,0);
declare @audit_action varchar(100);
select @CutoffId=i.CutoffId from inserted i;
select @Cutoff=i.cutoff from inserted i;
select @CollegeId=i.CollegeId from inserted i;
select @degreeid=i.degreeid from inserted i;
select @streamid=i.streamid from inserted i;
select @entranceid=i.entranceid from inserted i;
select @gender=i.gender from inserted i;
select @UniversityType=i.UniversityType from inserted i;
select @capround=i.capround from inserted i;
--select @CollegeCode=i.CollegeCode from inserted i;
--select @Rank=i.Rank from inserted i;
set @audit_action='Inserted Record -- After Insert Trigger.';
insert into College_Maharashtra_BEngcutoffmaster1([CutoffId],cutoff,collegeid,degreeid,streamid,entranceid,gender,UniversityType,Capround,collegecode,Audit_Timestamp)
select i.[CutoffId],[Cutoff],[CollegeId],[DegreeId],[StreamId],[EntranceId],[Gender],[UniversityType],[Capround],
b.collegecode,'Inserted Record -- After Insert Trigger.'
from inserted i, College_CutoffCodeTransition b
where i.cutoffid=b.cutoffid
PRINT 'AFTER INSERT trigger fired.'
but here trigger not inserting any value :doze:
Thanks & Regards,
Pallavi
March 30, 2012 at 3:45 am
Pallavi,
I have a couple of questions,
1 . You said there will be only one record for one cutoffid in College_CutoffCodeTransition and cutoffid is identity in [College_CutoffMaster] how come you get a record before inserting into College_CutoffCodeTransition ?
2. Is it cutoffid or else cutoff alone in College_CutoffCodeTransition ?
Please do reply soon so tht i can revert back with some solution.
In the mean time can you please take the rough version which is given below.
ALTER trigger [dbo].[trg_After_Cutoffmaster_Insert] on
[dbo].[College_CutoffMaster]
AFTER INSERT
AS
declare @audit_action nvarchar(100)
set @audit_action='Inserted Record -- After Insert Trigger.';
insert into College_Maharashtra_BEngcutoffmaster1
([CutoffId],cutoff,collegeid,degreeid,streamid,entranceid,gender,UniversityType,Capround,collegecode,Audit_Timestamp)
selecta.[CutoffId],[Cutoff],[CollegeId],[DegreeId],[StreamId],[EntranceId],[Gender],[UniversityType],[Capround],
b.collegecode,@audit_action
frominserted a
LEFT JOIN College_CutoffCodeTransition b
ONa.cutoffid=b.cutoffid
PRINT 'AFTER INSERT trigger fired.'
GO
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply