| Author |
 Topic  |
|
|
KingCarlos
Yak Posting Veteran
Australia
60 Posts |
 Posted - 06/20/2012 : 21:07:48
|
Can anyone assist with my trigger? I basically have an insert script that I want to automate with a trigger, but I cannot get the trigger to work? - any help greatly appreciated.
CREATE TRIGGER [dbo].[Trigger2] ON [dbo].[contacts]
insert into Opt_out (contactid, firstname, lastname, email, phone, dcreated, dchanged, date)
select contacts.contactid, contacts.cfirstname, contacts.clastname, contacts.cemail1, contacts.cphone1, contacts.dcreated,
contacts.dchanged, contacts.dchanged
from contacts
where contacts.iyesno1 = '1' and dchanged >= dateadd(day, datediff(day, 0, getdate()),0 ) |
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47173 Posts |
Posted - 06/20/2012 : 21:36:25
|
your trigger doesnt make much sense. As of now it inserts entire records from contacts sstisfying where condition each time you do a DML operation (i dont know what operation as you've not specified it in posted code above. it should be either insert,update or delete).
Also I think you should be using temporary tables inserted,deleted inside trigger to capture only rows affected by DML operation
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
 |
|
|
KingCarlos
Yak Posting Veteran
Australia
60 Posts |
Posted - 06/20/2012 : 21:41:52
|
I managed to figure it out
USE Webinar_JJ;
GO
IF OBJECT_ID ('Trigger4','TR') IS NOT NULL
DROP TRIGGER Trigger4;
GO
-- This trigger prevents a row from being inserted in the Purchasing.PurchaseOrderHeader table
-- when the credit rating of the specified vendor is set to 5 (below average).
CREATE TRIGGER Trigger4 ON Contacts
AFTER UPDATE
AS
IF EXISTS (select contacts.contactid, contacts.cfirstname, contacts.clastname, contacts.cemail1, contacts.cphone1, contacts.dcreated,
contacts.dchanged, contacts.dchanged
from contacts (nolock)
where contacts.iyesno1 = '1' and dchanged >= dateadd(day, datediff(day, 0, getdate()),0 )
)
BEGIN
RETURN
END;
GO
-- This statement attempts to insert a row into the PurchaseOrderHeader table
-- for a vendor that has a below average credit rating.
-- The AFTER INSERT trigger is fired and the INSERT transaction is rolled back.
insert into Opt_out (contactid, firstname, lastname, email, phone, dcreated, dchanged, date)
select contacts.contactid, contacts.cfirstname, contacts.clastname, contacts.cemail1, contacts.cphone1, contacts.dcreated,
contacts.dchanged, contacts.dchanged
from contacts
where contacts.iyesno1 = '1' and dchanged >= dateadd(day, datediff(day, 0, getdate()),0 )
;
GO
|
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47173 Posts |
Posted - 06/20/2012 : 21:45:04
|
sorry still this looks a bit odd!
...
IF EXISTS (select contacts.contactid, contacts.cfirstname, contacts.clastname, contacts.cemail1, contacts.cphone1, contacts.dcreated,
contacts.dchanged, contacts.dchanged
from contacts (nolock)
where contacts.iyesno1 = '1' and dchanged >= dateadd(day, datediff(day, 0, getdate()),0 )
)
as it still looks for entire table not records that are affected by update alone
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
KingCarlos
Yak Posting Veteran
Australia
60 Posts |
Posted - 06/20/2012 : 22:27:31
|
Hi there,
I was hoping my SQL trigger would see a record change and then insert a record into a new table, but as you can see it is not exactly working.
Any help appreciated. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47173 Posts |
Posted - 06/20/2012 : 22:44:56
|
can you first explain your full scenario? for each update, what exactly you want to trigger to do?
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
KingCarlos
Yak Posting Veteran
Australia
60 Posts |
Posted - 06/20/2012 : 23:05:18
|
Sure, the trigger concept is quite simple.
When the contacts.iyesno1 field changes from 0 to 1 then I want to extract some of that data from the contacts table and insert it into the opt_out table.
Everything works except I have to keep running the trigger. Instead I want the trigger to sit there, see the change and then act. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47173 Posts |
Posted - 06/20/2012 : 23:09:52
|
As per your explanation below is sufficient
USE Webinar_JJ;
GO
IF OBJECT_ID ('Trigger4','TR') IS NOT NULL
DROP TRIGGER Trigger4;
GO
-- This trigger prevents a row from being inserted in the Purchasing.PurchaseOrderHeader table
-- when the credit rating of the specified vendor is set to 5 (below average).
CREATE TRIGGER Trigger4 ON Contacts
AFTER UPDATE
AS
BEGIN
INSERT INTO opt_out (contactid, firstname, lastname, email, phone, dcreated, dchanged, date)
select i.contactid, i.cfirstname, i.clastname, i.cemail1, i.cphone1, i.dcreated, i.dchanged, i.dchanged
from inserted i
inner join deleted d
on i.contactid = d.contactid
where i.iyesno1 = '1'
and d.iyesno1 = '0'
END
GO
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
| |
Topic |
|
|
|
INSERT TRIGGER
