第一次发布这样的道歉,如果这已经被问到了。
我在Server中有一个表,该表正在从许多不同的位置更新,其中包含我不想在表中使用的数据。我得出的结论是,我需要一个触发器,它将在更新之前触发,以检查email_address是否有效。
我有两张桌子被跟踪和trackOmit。
Tracked
id
email_address
ip_address
trackedPage
trackOmit
id
trackOmit_emailTrackOmit是一个电子邮件域"hotmail.com,gmail.co.uk“等列表,无论出于什么原因我们都不想跟踪,但是由于设计错误,update语句没有考虑到trackOmit表。我不知道从哪里开始编写触发器,以便在更新前对照trackOmit表检查更新后的值。
谢谢你能提供的任何帮助。
发布于 2018-12-04 12:03:32
下面是一个INSTEAD OF TRIGGER的例子。
--demo setup
USE [Test]
GO
drop table if exists Tracked;
drop table if exists TrackOmit;
Create table Tracked (id int, email_address varchar(100), ip_address varchar(50), trackedPage varchar(100))
create table TrackOmit (id int, trackOmit_email varchar(100))
insert into Tracked(id, email_address, ip_address, trackedPage) values
(1,'test@gmail.com','10.100.100.100', ' ')
insert into TrackOmit(id,trackOmit_email) values(1,'hotmail.com'),(2,'gmail.co.uk')
-----------------
--create instead of trigger
DROP TRIGGER IF EXISTS [dbo].[TrackedInsteadOfUpdate];
GO
CREATE TRIGGER [dbo].[TrackedInsteadOfUpdate] ON [dbo].[Tracked]
INSTEAD OF UPDATE
AS
BEGIN
SET NOCOUNT ON
--only update rows where the updated email_address
--is not on the TrackOmit table
UPDATE t
SET t.email_address = i.email_address
FROM dbo.tracked t
JOIN inserted i
ON i.id = t.id
AND NOT EXISTS (
SELECT *
FROM TrackOmit o
WHERE i.Email_Address LIKE '%' + o.trackOmit_email + '%'
)
END
GO
ALTER TABLE [dbo].[Tracked] ENABLE TRIGGER [TrackedInsteadOfUpdate]
GO--original values in Tracked
select * from Tracked
| id | email_address | ip_address | trackedPage |
|----|-----------------|----------------|-------------|
| 1 | test@gmail.com | 10.100.100.100 | |--Attempt update for hotmail.com - should not update
update Tracked set email_address = 'test@hotmail.com'
select * from Tracked
| id | email_address | ip_address | trackedPage |
|----|-----------------|----------------|-------------|
| 1 | test@gmail.com | 10.100.100.100 | |--Attempt update for gmail.com - should update
update Tracked set email_address = 'test1@gmail.com'
select * from Tracked
| id | email_address | ip_address | trackedPage |
|----|-----------------|----------------|-------------|
| 1 | test1@gmail.com | 10.100.100.100 | |--Attempt update for gmail.co.uk - should not update
update Tracked set email_address = 'test2@gmail.co.uk'
select * from Tracked
| id | email_address | ip_address | trackedPage |
|----|-----------------|----------------|-------------|
| 1 | test1@gmail.com | 10.100.100.100 | |https://dba.stackexchange.com/questions/224077
复制相似问题