The UPDATE part of the trigger is fine, though I prefer to use JOIN
UPDATE monitoring.dbo.tblApplicationList
SET ApplicationName = I.resdesc
FROM monitoring.dbo.tblApplicationList al
JOIN Inserted I ON al.pk_appid = I.pk_resource
The INSERT part has 2 problems:
It doesn't check if record already exists in the monitoring table as your original VFP code shows
SQL Server trigers fire once per SQL statement not per a row. If multiple records are inserted with INSERT INTO...SELECT FROM statement the trigger will fail.
>Thanks Sergey - as it turns out spent a few minutes reading the SQL Server Help (books online gizmo and came up with this...which seems to work just fine...>CREATE TRIGGER [tblPopulate_Monitoring_tiud1] ON dbo.resources
>FOR INSERT, UPDATE
>AS
>
>DECLARE @pk_resource INT,
> @resdesc VARCHAR(60)
>
>IF UPDATE (resdesc)
> BEGIN
> UPDATE monitoring.dbo.tblApplicationList
> SET ApplicationName = I.resdesc
> FROM Inserted I, monitoring.dbo.tblApplicationList al
> WHERE al.pk_appid = I.pk_resource
> END
>
>
>IF NOT Exists(select I.pk_resource from INSERTED I,
monitoring.dbo.tblApplicationList al WHERE I.pk_resource = al.pk_appid)
> BEGIN
> SET @pk_resource = (SELECT I.pk_resource FROM INSERTED I)
> SET @resdesc = (SELECT I.resdesc FROM INSERTED I)
>
> INSERT INTO monitoring.dbo.tblApplicationList (pk_appid,
ApplicationName) VALUES (@pk_resource, @resdesc)
> END
>
>
>>Try>>CREATE TRIGGER utr_resources_Insert
>>ON resources
>>FOR INSERT
>>AS
>>BEGIN
>> INSERT INTO Monitoring (pk_appid, ApplicationName)
>> SELECT pk_resource, resdesc FROM INSERTED
>> WHERE NOT EXISTS( SELECT * FROM Monitoring
>> WHERE Monitoring.pk_appid = INSERTED.pk_resource)
>>
>>END
>>
--sb--
