Creating Trigger

Question

Creating Trigger

Viewing 15 posts - 16 through 30 (of 31 total)

You must be logged in to reply to this topic. Login to reply

Alvin Ramard SSC-Forever Points: 41190 More actions · Answer 1

Lynn Pettis (11/30/2015)
Alvin Ramard (11/30/2015)
Lynn Pettis (11/30/2015)
Pulling hen's teeth, but based on what little detail you have been willing to provide, does the following help?
create table dbo.FirstTable(
ColA int,
ColB int,
ColC datetime null);
create table dbo.SecondTable(
ColA int,
ColB int,
ColC datetime null);
go
create trigger dbo.DataMove on dbo.FirstTable for insert, update
as
insert into dbo.SecondTable(ColA,ColB,Colc)
select ins.ColA, ins.ColB, ins.ColC
from inserted ins
where ins.ColC > '' or ins.ColC <> (select del.ColC from deleted del where del.ColA = ins.ColA);
go
insert into dbo.FirstTable(ColA,ColB,ColC)
values
(446063,6543,NULL),
(443107,6543,NULL),
(3034 ,1152,NULL),
(443451,6543,NULL),
(440501,6543,NULL),
(9973 ,515 ,'2005-10-04 14:34:00.673'),
(10650 ,515 ,'2005-10-04 14:34:19.953'),
(10651 ,515 ,'2005-10-04 14:35:09.343'),
(15289 ,511 ,'2005-10-25 11:30:31.227'),
(15334 ,511 ,'2005-10-25 11:30:50.600');
select * from dbo.FirstTable;
select * from dbo.SecondTable;
update dbo.FirstTable set
ColC = getdate()
where ColA = 3034;
select * from dbo.FirstTable;
select * from dbo.SecondTable;
I am sure there is more involved, but you really haven't been forth coming with any details.
I think we need an additional condition in the where clause:
and ins.ColC IS NOT NULL
Why? If ins.ColC > '' it isn't NULL.
I could seeing changing it to ins.ColC is not null.

Is ins.Colc > '' when ins.ColC is null? If so, then you would be correct. I just don't like comparing nulls to anything.

Another option would be:

ISNULL(ins.ColC, '') > ''

Alvin Ramard
Memphis PASS Chapter[/url]

All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.

For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]

PJ_SQL SSCertifiable Points: 6703 More actions · Answer 2

PJ_SQL

SSCertifiable

Points: 6703

November 30, 2015 at 3:39 pm

#1843043

Thank you so much.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 3

Lynn Pettis (11/30/2015)
Alvin Ramard (11/30/2015)
Lynn Pettis (11/30/2015)
Pulling hen's teeth, but based on what little detail you have been willing to provide, does the following help?
create table dbo.FirstTable(
ColA int,
ColB int,
ColC datetime null);
create table dbo.SecondTable(
ColA int,
ColB int,
ColC datetime null);
go
create trigger dbo.DataMove on dbo.FirstTable for insert, update
as
insert into dbo.SecondTable(ColA,ColB,Colc)
select ins.ColA, ins.ColB, ins.ColC
from inserted ins
where ins.ColC > '' or ins.ColC <> (select del.ColC from deleted del where del.ColA = ins.ColA);
go
insert into dbo.FirstTable(ColA,ColB,ColC)
values
(446063,6543,NULL),
(443107,6543,NULL),
(3034 ,1152,NULL),
(443451,6543,NULL),
(440501,6543,NULL),
(9973 ,515 ,'2005-10-04 14:34:00.673'),
(10650 ,515 ,'2005-10-04 14:34:19.953'),
(10651 ,515 ,'2005-10-04 14:35:09.343'),
(15289 ,511 ,'2005-10-25 11:30:31.227'),
(15334 ,511 ,'2005-10-25 11:30:50.600');
select * from dbo.FirstTable;
select * from dbo.SecondTable;
update dbo.FirstTable set
ColC = getdate()
where ColA = 3034;
select * from dbo.FirstTable;
select * from dbo.SecondTable;
I am sure there is more involved, but you really haven't been forth coming with any details.
I think we need an additional condition in the where clause:
and ins.ColC IS NOT NULL
Why? If ins.ColC > '' it isn't NULL.
I could seeing changing it to ins.ColC is not null.

Run the code for yourself and see.

Alvin Ramard SSC-Forever Points: 41190 More actions · Answer 4

Lynn Pettis (11/30/2015)
Lynn Pettis (11/30/2015)
Alvin Ramard (11/30/2015)
Lynn Pettis (11/30/2015)
Pulling hen's teeth, but based on what little detail you have been willing to provide, does the following help?
create table dbo.FirstTable(
ColA int,
ColB int,
ColC datetime null);
create table dbo.SecondTable(
ColA int,
ColB int,
ColC datetime null);
go
create trigger dbo.DataMove on dbo.FirstTable for insert, update
as
insert into dbo.SecondTable(ColA,ColB,Colc)
select ins.ColA, ins.ColB, ins.ColC
from inserted ins
where ins.ColC > '' or ins.ColC <> (select del.ColC from deleted del where del.ColA = ins.ColA);
go
insert into dbo.FirstTable(ColA,ColB,ColC)
values
(446063,6543,NULL),
(443107,6543,NULL),
(3034 ,1152,NULL),
(443451,6543,NULL),
(440501,6543,NULL),
(9973 ,515 ,'2005-10-04 14:34:00.673'),
(10650 ,515 ,'2005-10-04 14:34:19.953'),
(10651 ,515 ,'2005-10-04 14:35:09.343'),
(15289 ,511 ,'2005-10-25 11:30:31.227'),
(15334 ,511 ,'2005-10-25 11:30:50.600');
select * from dbo.FirstTable;
select * from dbo.SecondTable;
update dbo.FirstTable set
ColC = getdate()
where ColA = 3034;
select * from dbo.FirstTable;
select * from dbo.SecondTable;
I am sure there is more involved, but you really haven't been forth coming with any details.
I think we need an additional condition in the where clause:
and ins.ColC IS NOT NULL
Why? If ins.ColC > '' it isn't NULL.
I could seeing changing it to ins.ColC is not null.
Run the code for yourself and see.

Looks like it works. Just call me picky when it comes to dealing with nulls. 🙂

Alvin Ramard
Memphis PASS Chapter[/url]

All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.

For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 5

Alvin Ramard (11/30/2015)
Lynn Pettis (11/30/2015)
Lynn Pettis (11/30/2015)
Alvin Ramard (11/30/2015)
Lynn Pettis (11/30/2015)
Pulling hen's teeth, but based on what little detail you have been willing to provide, does the following help?
create table dbo.FirstTable(
ColA int,
ColB int,
ColC datetime null);
create table dbo.SecondTable(
ColA int,
ColB int,
ColC datetime null);
go
create trigger dbo.DataMove on dbo.FirstTable for insert, update
as
insert into dbo.SecondTable(ColA,ColB,Colc)
select ins.ColA, ins.ColB, ins.ColC
from inserted ins
where ins.ColC > '' or ins.ColC <> (select del.ColC from deleted del where del.ColA = ins.ColA);
go
insert into dbo.FirstTable(ColA,ColB,ColC)
values
(446063,6543,NULL),
(443107,6543,NULL),
(3034 ,1152,NULL),
(443451,6543,NULL),
(440501,6543,NULL),
(9973 ,515 ,'2005-10-04 14:34:00.673'),
(10650 ,515 ,'2005-10-04 14:34:19.953'),
(10651 ,515 ,'2005-10-04 14:35:09.343'),
(15289 ,511 ,'2005-10-25 11:30:31.227'),
(15334 ,511 ,'2005-10-25 11:30:50.600');
select * from dbo.FirstTable;
select * from dbo.SecondTable;
update dbo.FirstTable set
ColC = getdate()
where ColA = 3034;
select * from dbo.FirstTable;
select * from dbo.SecondTable;
I am sure there is more involved, but you really haven't been forth coming with any details.
I think we need an additional condition in the where clause:
and ins.ColC IS NOT NULL
Why? If ins.ColC > '' it isn't NULL.
I could seeing changing it to ins.ColC is not null.
Run the code for yourself and see.
Looks like it works. Just call me picky when it comes to dealing with nulls. 🙂

My problem is working with code that uses ISNULL(<somecol>,'') <> '' and <somecol> > '' (or some other value or variable value) forcing table scans when there is an index on <somecol> that could easily be used if the ISNULL is dropped.

himanshu.sinha Right there with Babe Points: 739 More actions · Answer 6

This should work for you .

CREATE TABLE _A

(

A int ,

B int ,

c datetime

)

CREATE TABLE _B

(

A int ,

B int ,

c datetime

)

Insert into _A (A,B,C)

SELECT 44601631,6543,null

Insert into _A (A,B,C)

SELECT 44601631,6543,GETDATE()

CREATE TRIGGER TRG_A

ON _A

AFTER INSERT, UPDATE

AS

insert into _B

SELECT * FROM inserted

where c is not null

SELECT * FROM _A

SELECT * from _B

Update _A

Set c = Getdate()

where A = 44601631

GO

himanshu.sinha Right there with Babe Points: 739 More actions · Answer 7

This should work for you .

CREATE TABLE _A

(

A int ,

B int ,

c datetime

)

CREATE TABLE _B

(

A int ,

B int ,

c datetime

)

Insert into _A (A,B,C)

SELECT 44601631,6543,null

Insert into _A (A,B,C)

SELECT 44601631,6543,GETDATE()

CREATE TRIGGER TRG_A

ON _A

AFTER INSERT, UPDATE

AS

insert into _B

SELECT * FROM inserted

where c is not null

SELECT * FROM _A

SELECT * from _B

Update _A

Set c = Getdate()

where A = 44601631

GO

PJ_SQL SSCertifiable Points: 6703 More actions · Answer 8

Can I do join inside trigger?If I had to join inserted with other table from different database is it possible?

CREATE TRIGGER TRG_A

ON _A

AFTER INSERT, UPDATE

AS

insert into _B

SELECT * FROM inserted

where c is not null

Sean Lange SSC Guru Points: 286590 More actions · Answer 9

PJ_SQL (12/2/2015)
Can I do join inside trigger?If I had to join inserted with other table from different database is it possible?
CREATE TRIGGER TRG_A
ON _A
AFTER INSERT, UPDATE
AS
insert into _B
SELECT * FROM inserted
where c is not null

Of course it is. It is just a query. Of course the easiest way to get an answer to this type of thing would be to just try it. 😉

_______________________________________________________________

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/

PJ_SQL SSCertifiable Points: 6703 More actions · Answer 10

PJ_SQL

SSCertifiable

Points: 6703

December 2, 2015 at 10:21 am

#1843578

Thank you I was able to do it.

PJ_SQL SSCertifiable Points: 6703 More actions · Answer 11

What needs to be done to copy the old record before change.

For one that has been updated I used trigger to insert into new table.

But How do I also insert old record before it was updated?

Sergiy SSC Guru Points: 110208 More actions · Answer 12

PJ_SQL (12/15/2015)
What needs to be done to copy the old record before change.
For one that has been updated I used trigger to insert into new table.
But How do I also insert old record before it was updated?

Records as they were before update may be found in table "deleted" inside of the trigger.

_____________
Code for TallyGenerator

PJ_SQL SSCertifiable Points: 6703 More actions · Answer 13

Thank you I am new to this. Can you provide details?

Sergiy SSC Guru Points: 110208 More actions · Answer 14

SELECT i.PK_Column, d.OtherColumn [Old Value], i.OtherColumn [New Value]

FROM inserted I

INNER JOIN deleted d on d.PK_Column = i.PK_column

This would work in FOR UPDATE trigger only.

On INSERT table deleted is empty, and on DELETE inserted is empty.

If you use the same trigger for all 3 actions you need to use FULL OUTER JOIN

_____________
Code for TallyGenerator

PJ_SQL SSCertifiable Points: 6703 More actions · Answer 15

This is my trigger:

CREATE TRIGGER TRG_A

ON _A

AFTER INSERT, UPDATE

AS

insert into _B

SELECT * FROM inserted

where c is not null

How do I incorporate in this?

Thank you.